psql Cheatsheet

Top commands.

Connecting

psql -h host -U user -d dbname. Standard connection. Password prompted unless PGPASSWORD set.

psql 'postgresql://user:pass@host:5432/db?sslmode=require'. URL connection string format. Required for SSL-enforced connections.

.pgpass file for password storage. ~/.pgpass with mode 600. Avoids password prompts and CLI history exposure.

Inspecting structure

\dt lists tables in current schema. \dt schema.* lists tables in a schema.

\d table_name describes table structure. \d+ adds size and constraint detail.

\df lists functions. \dn lists schemas. \du lists roles.

Query patterns

\timing on enables query timing display. Useful for performance investigation.

EXPLAIN ANALYZE select_query. Shows query plan and actual execution stats. First step in slow query debugging.

\x toggle expanded output. Useful when rows are wide; one column per line.

Admin queries

SELECT * FROM pg_stat_activity. Active connections; useful for finding long-running or stuck queries.

SELECT * FROM pg_locks. Lock state; identify blocking queries during contention.

SELECT * FROM pg_stat_user_tables. Per-table read/write/vacuum stats. Identifies hot tables and bloat.

Scripting

psql -c 'SELECT ...' database. Single-shot query; useful in shell scripts.

psql -f script.sql. Run a script file. Pipe stdin for inline scripts.

psql --csv -c 'SELECT ...'. CSV output mode; pipe to file or other tools.