psql meta-commands cheat sheet

Besides SQL, psql understands meta-commands: lines that start with a backslash and are handled by psql itself, not by the server. They do not need a semicolon. Most listing commands accept a pattern (\dt sales.*), a + suffix for more detail and an S modifier to include system objects.

Starting psql

psql -h db1.example.com -p 5432 -U appuser -d appdb
psql "host=db1.example.com dbname=appdb user=appuser sslmode=require"
psql -d appdb -c 'SELECT count(*) FROM orders'
psql -d appdb -f migrate.sql
psql -X -v ON_ERROR_STOP=1 -d appdb -f migrate.sql

• -h host (a value starting with / is a socket directory), -p port, -U user, -d database or a full connection string.
• -c runs one command and exits; -f runs a file.
• -X skips your ~/.psqlrc, which makes scripts behave the same everywhere.
• -v ON_ERROR_STOP=1 stops a script at the first error; psql then exits with status 3.

Connection and help

• \c dbname [user]: connect to another database (and optionally as another user).
• \conninfo: show the current connection, including SSL details.
• \?: list all meta-commands; \? variables lists psql variables.
• \h CREATE INDEX: SQL syntax help for a command.
• \q: quit.

Listing and describing objects

• \l: databases, with owner, encoding and privileges.
• \dn: schemas.
• \dt, \dv, \di, \ds: tables, views, indexes, sequences.
• \d name: columns, types, indexes and constraints of one table or view. \d+ name adds storage and size information.
• \df: functions.
• \du (same as \dg): roles and their attributes.
• \dp (alias \z): privileges on tables, views and sequences.
• \dx: installed extensions.

\dt public.*
\d+ orders
\di *orders*
\du
\dp orders

Output and timing

• \x toggles expanded output (one column per line), useful for wide rows. \x auto switches only when a row does not fit.
• \timing on prints how long each statement takes.
• \gx instead of ; runs the query once with expanded output.
• \watch 5 re-runs the last query every 5 seconds until you press Ctrl+C (the default is 2 seconds).
• \o file.txt sends query results to a file; \o alone switches back to the screen.

\timing on
SELECT pid, state, query FROM pg_stat_activity \gx
SELECT count(*) FROM pg_stat_activity WHERE state = 'active' \watch 5

Files, editing and variables

• \i file.sql: run commands from a file.
• \e: open the current query in your editor ($EDITOR), then run it when you save and close.
• \copy: client-side COPY. It reads or writes files on your machine, so it needs no server file privileges.
• \set name value sets a psql variable; reference it as :name or :'name' (quoted literal) in SQL.
• \echo prints text, handy in scripts.
• \gexec runs each value of the result as a new SQL statement.
• \password appuser changes a password without sending it in clear text in an SQL statement.
• \s shows your command history.

\copy orders TO 'orders.csv' WITH (FORMAT csv, HEADER)
\set customer_id 42
SELECT * FROM orders WHERE customer_id = :customer_id;
SELECT format('ANALYZE %I.%I', schemaname, tablename) FROM pg_tables WHERE schemaname = 'public' \gexec

Passwords without typing them

For scripts, put credentials in ~/.pgpass (one line per server, format hostname:port:database:username:password, * as a wildcard). libpq ignores the file unless only the owner can read it:

echo 'db1.example.com:5432:appdb:appuser:CHANGE_ME' >> ~/.pgpass
chmod 0600 ~/.pgpass

On Windows the file is %APPDATA%\postgresql\pgpass.conf. The PGPASSFILE environment variable points to a different file.

Official documentation

• psql reference
• The password file (.pgpass)