Mastering psql — Command-Line Power Tips
psql Cheat-Sheet & Power User Guide
1 · Startup Sequence
- Environment –
PGHOST, PGPORT, PGUSER, PGDATABASE are honoured.
- User profile – reads
$HOME/.psqlrc (skip with -X).
- Single-shot execution
-f FILE – run file then quit-c "COMMAND" – inline SQL / meta cmd then quit
--help prints all options · --version shows build info.
2 · Line Editing & History
- Arrow-up / down cycles past commands (libreadline).
- Tab completion on Unix (SQL keywords, objects, filenames).
| History & Buffer | Description |
|---|
\s | Show command history (same as ~/.psql_history) |
\s FILE | Save history to FILE |
\e | Edit current query buffer in $EDITOR, then execute |
\e FILE | Open FILE in editor, then execute contents |
\w FILE | Write current buffer to FILE (do not execute) |
3 · Controlling Output
| Command | Effect |
|---|
-o FILE or \o FILE | Redirect query STDOUT → FILE (append with \o >>FILE) |
\g FILE | Execute buffer, send results to FILE |
\watch <secs> | Re-run the previous query every n seconds (handy for monitoring) |
4 · Variables & Substitution
\set name value creates a variable.- Reference via
:name in SQL or \echo :name.
\unset name removes it.
| Special Variable | Meaning |
|---|
AUTOCOMMIT | off → wrap every stmt in an open transaction ; must
COMMIT/ROLLBACK manually. |
ENCODING | Client encoding override (UTF8, WIN1254…) |
HISTFILE | Alternate history path |
ON_ERROR_STOP | Abort batch/pipe on first error (good for scripts) |
PROMPT1/2 | Custom interactive prompt strings |
VERBOSITY | Error message detail (default|verbose|terse) |
5 · Conditional Blocks (for scripts)
\if :city = 'Edmonton'
SELECT 'Hello YEG';
\elif :city = 'Calgary'
SELECT 'Hello YYC';
\else
\echo 'Unknown city'
\endif
6 · Information-Lookup Commands
| Meta Cmd | Shortcut | What it Lists / Describes |
|---|
\d | (none) | Tables, views, sequences, indexes |
\d+ | | …with size & extra details |
\dt | | Tables only (i s t v I S flags per pattern) |
\d mytable | | Describe columns & constraints |
\l | \list | Databases in the cluster (\l+ adds size) |
\dn+ | | Schemas with ACL & comments |
\df+ | | Functions with owner, language, source |
7 · Every-Day Meta Commands
| Meta Cmd | Purpose |
|---|
\conninfo | Show current host · port · DB · user |
\q / Ctrl-D | Quit psql |
\cd [dir] | Change working directory (\! pwd to view) |
\! shellcmd | Run OS command without leaving psql |
8 · Putting It Together — Sample Session
$ psql # env picks up PGHOST/PORT/USER
psql (16.2)
Type "help" for help.
postgres=# \set city Edmonton
postgres=# \if :city = 'Edmonton'
postgres-# SELECT current_time;
postgres-# \endif
current_time
-------------------
13:37:42.152643-06
(1 row)
postgres=# \o result.txt -- redirect following output
postgres=# SELECT pg_size_pretty(pg_database_size(current_database()));
postgres=# \o -- back to stdout
postgres=# \watch 5 -- refresh every 5 s
Pro tip 🛠️ — Add alias pglog="tail -f /var/lib/pgsql/16/data
Comments
Post a Comment