Contents
Achievements
pg_achievements ships 68 builtin achievements. Each one is unlocked when a counter of the role reaches the threshold; the counters are described in the README. Secret achievements show ??? instead of the description in achievements.progress until they are unlocked.
Bronze — 10 points, first steps
| Code | Title | Description | Condition |
|---|---|---|---|
hello_world |
Hello, World | Run your first SELECT. | stmt_select ≥ 1 |
welcome |
Welcome | Connect to the server for the first time. | connect ≥ 1 |
first_table |
First Table | Create your first table. | create_table ≥ 1 |
first_index |
Indexer | Create your first index. | create_index ≥ 1 |
first_function |
Functionary | Create your first function or procedure. | create_function ≥ 1 |
first_cte |
WITH Love | Run a query with a common table expression. | cte_any ≥ 1 |
first_join |
All Connected | Run a query with an explicit JOIN. | join_count_max ≥ 1 |
first_explain |
Investigator | Ask the planner with EXPLAIN. | explain_any ≥ 1 |
typo_apprentice |
Learning from Typos | Make 10 syntax errors. | err_syntax ≥ 10 |
star_gazer |
Stargazer | Run 100 queries with SELECT *. | select_star ≥ 100 |
night_owl |
Night Owl | Run a query between 03:00 and 05:00. | night_query ≥ 1 |
null_not_equal |
NULL Is Not Equal to NULL | Compare with = NULL instead of IS NULL. | null_eq ≥ 1 |
ctrl_z |
Ctrl+Z | Roll back a transaction with ROLLBACK. | rollback ≥ 1 |
upsert |
Upsert Happens | Run INSERT … ON CONFLICT. | upsert ≥ 1 |
returning_hero |
Returning Hero | Use RETURNING in a data-modifying statement. | returning ≥ 1 |
self_documenting |
Self-Documenting | Describe an object with COMMENT ON. | comment_on ≥ 1 |
Silver — 25 points, mastering PostgreSQL features
| Code | Title | Description | Condition |
|---|---|---|---|
detective |
Detective | Run EXPLAIN ANALYZE. | explain_analyze ≥ 1 |
recursion |
Recursion: See Recursion | Run a WITH RECURSIVE query. | cte_recursive ≥ 1 |
open_window |
Open Window | Use a window function. | window_func ≥ 1 |
lateral_thinking |
Lateral Thinking | Use LATERAL. | lateral ≥ 1 |
jsonb_juggler |
JSONB Juggler | Use the JSONB operators ->, ->>, @> or ? 10 times. | jsonb_ops ≥ 10 |
patient |
Patient | Wait for a query that runs longer than a minute. | query_over_60s ≥ 1 |
no_locks |
No Locks | Create an index CONCURRENTLY. | create_index_concurrently ≥ 1 |
trigger_happy |
Trigger Happy | Create a trigger. | create_trigger ≥ 1 |
divide_and_conquer |
Divide and Conquer | Create a partitioned table or a partition. | partition_create ≥ 1 |
extender |
Expanding Horizons | Create 3 extensions. | create_extension ≥ 3 |
thousand_and_one |
A Thousand and One Queries | Run 1001 SELECT statements. | stmt_select ≥ 1001 |
night_regular |
Night Shift | Run 20 queries between 03:00 and 05:00. | night_query ≥ 20 |
star_veteran |
Star Veteran | Run 1000 queries with SELECT *. | select_star ≥ 1000 |
merge_conflict |
Merge Conflict | Run a MERGE statement. | merge ≥ 1 |
save_point |
Save Point | Roll back to a savepoint. | rollback_to_savepoint ≥ 1 |
materialist |
Materialist | Refresh a materialized view CONCURRENTLY. | refresh_matview_concurrently ≥ 1 |
queue_master |
Queue Master | Lock rows with SKIP LOCKED. | skip_locked ≥ 1 |
cube_master |
Cube Master | Use GROUPING SETS, ROLLUP or CUBE. | grouping_sets ≥ 1 |
big_brother |
Big Brother | Create a row-level security policy. | create_policy ≥ 1 |
are_you_listening |
Are You Listening? | Use LISTEN, NOTIFY or pg_notify(). | listen_notify ≥ 1 |
bronze_collector |
Collector | Collect every bronze achievement. | every builtin bronze achievement |
Gold — 50 points, risky, rare and legendary events
| Code | Title | Description | Condition |
|---|---|---|---|
forgot_where |
Forgot WHERE | UPDATE without a WHERE clause. | update_no_where ≥ 1 |
forgot_where_again |
Forgot WHERE, Part 2 | DELETE without a WHERE clause. | delete_no_where ≥ 1 |
mutual_understanding |
Mutual Understanding | Take part in a deadlock. | deadlock ≥ 1 |
friday_deploy |
Friday Deploy | Run DDL on a Friday after 18:00. | ddl_friday_evening ≥ 1 |
index_for_the_weak |
Indexes Are for the Weak | Scan a table of a million rows sequentially. | seqscan_big ≥ 1 |
executioner |
Executioner | Call pg_terminate_backend. | terminate_backend ≥ 1 |
clean_slate |
Clean Slate | TRUNCATE a table. | truncate ≥ 1 |
table_owner |
Master of the Table | Run VACUUM FULL. | vacuum_full ≥ 1 |
serial_conflict |
Conflict of Interest | Hit a serialization failure. | serialization_failure ≥ 1 |
many_joins |
JOINs Without Borders | Run a query with 8 JOINs. | join_count_max ≥ 8 |
table_collector |
Table Collector | Create 100 tables. | create_table ≥ 100 |
deadlock_veteran |
Deadlock Veteran | Take part in 5 deadlocks. | deadlock ≥ 5 |
yolo |
You Only Live Once 🔒 | Drop a database. | drop_database ≥ 1 |
time_machine |
Time Machine 🔒 | Roll back a transaction that ran for over an hour. | rollback_long_xact ≥ 1 |
ten_joins |
Ten JOINs | Run a query with 10 JOINs. | join_count_max ≥ 10 |
scorched_earth |
Scorched Earth | Drop 50 tables. | drop_table ≥ 50 |
marathon |
Marathon 🔒 | Wait for a query that runs longer than an hour. | query_over_1h ≥ 1 |
serial_killer |
Serial Executioner 🔒 | Call pg_terminate_backend 10 times. | terminate_backend ≥ 10 |
where_is_my_table |
Where Is My Table? | Query a table that does not exist 50 times. | err_undefined_table ≥ 50 |
to_infinity |
To Infinity and Beyond | Divide by zero 10 times. | err_division_by_zero ≥ 10 |
cartesian_explosion |
Cartesian Explosion | Join tables without any join condition. | cartesian_product ≥ 1 |
not_in_trouble |
NOT IN Trouble | Use NOT IN with a subquery (mind the NULLs). | not_in_subquery ≥ 1 |
lock_hoarder |
Lock Hoarder | Take an ACCESS EXCLUSIVE lock with LOCK TABLE. | lock_exclusive ≥ 1 |
unicode_artist |
Unicode Artist 🔒 | Create a table with an emoji in its name. | emoji_name ≥ 1 |
novelist |
Novelist 🔒 | Name a table so long that PostgreSQL truncates the name. | long_name ≥ 1 |
happy_new_query |
Happy New Query 🔒 | Run a query in the first minute of a new year. | new_year_query ≥ 1 |
regret |
Regret 🔒 | Try to switch achievements off with ALTER SYSTEM. | disable_attempt ≥ 1 |
streak |
On a Roll | Run queries 30 days in a row. | streak_days ≥ 30 |
billionaire |
Billionaire | Process a billion rows. | rows_processed ≥ 1000000000 |
gold_rush |
Gold Rush | Collect every gold achievement. | every builtin gold achievement |
Platinum — 300 points, collect them all
| Code | Title | Description | Condition |
|---|---|---|---|
platinum_collection |
Slonik’s Chosen | Collect every other achievement. | every other builtin achievement |
🔒 — secret achievement.