TopolearnFluency in psql makes exploring schemas and debugging production databases much quicker than any GUI.
psql is far more than a prompt. The commands worth memorising:
- Describe:
\d table(columns, indexes, constraints),\d+(plus sizes and storage),\dnschemas,\duroles,\dffunctions,\diindexes,\dxextensions. Add a pattern:\dt sales.*. - Output:
\x autoswitches to one-column-per-line display when rows are too wide;\pset null '(null)'makes NULLs visible. - Timing:
\timing onprints how long each statement took. - Editing:
\eopens the last query in$EDITOR;\ef funcedits a function. - Scripts:
\i file.sqlruns a file; from the shell,psql -f file.sqldoes the same, and-v ON_ERROR_STOP=1stops at the first error (always use it in automation). - Variables:
\set id 42thenSELECT * FROM t WHERE id = :id;— or:'name'for a quoted literal. - Re-running:
\watch 2repeats the last query every two seconds, handy for monitoring. - Exporting:
\copy(next lessons) and\o fileto redirect output.
Settings you want every time go in ~/.psqlrc. \set ECHO_HIDDEN on shows the catalog queries behind \d commands — a great way to learn the system catalogs.
Example
# ~/.psqlrc
\set QUIET 1
\x auto
\timing on
\pset null '(null)'
\set ON_ERROR_STOP on
\set QUIET 0
# run a migration script safely from the shell
psql "postgresql://app@localhost/shop" -v ON_ERROR_STOP=1 -f migrate.sql
# inside psql
# shop=# \set uid 42
# shop=# SELECT * FROM orders WHERE user_id = :uid;
# shop=# SELECT count(*) FROM pg_stat_activity \watch 5Remember
- \d, \d+, \dn, \du, \df, \di describe objects
- \x auto and \timing on belong in every ~/.psqlrc
- Run scripts with -v ON_ERROR_STOP=1 so failures stop the script
Watch out for
- Running psql -f in CI without ON_ERROR_STOP, so a failed statement is ignored and the script 'succeeds'.