PostgreSQL Cheatsheet

psql Meta-Commands

Use this PostgreSQL reference while you build software engineering projects, review code, or refresh the syntax you reach for most.

Connecting

psql takes either flag-style arguments or a single connection URI. The URI form is what you paste from a hosted provider.

psql -h db.example.com -p 5432 -U app -d appdb
psql "postgres://app:secret@db.example.com:5432/appdb?sslmode=require"
psql "postgresql:///appdb?host=/var/run/postgresql"   # unix socket

Source	What it sets	Notes
`PGHOST` `PGPORT` `PGUSER` `PGDATABASE`	Connection defaults	Beat compiled-in defaults, lose to flags
`PGPASSWORD`	Password	Convenient, visible in `ps` and shell history
`~/.pgpass`	Password per host	`host:port:db:user:password`, must be chmod `0600`
`PGSERVICEFILE` + `PGSERVICE`	Named connection sets	`pg_service.conf` with `[prod]` blocks, then `psql service=prod`
`PGSSLMODE` / `?sslmode=`	TLS policy	`require` encrypts but skips verification, `verify-full` is the real one
`PGOPTIONS`	Server settings	`PGOPTIONS='-c statement_timeout=5s'`

~/.pgpass line, and the wildcard form:

db.example.com:5432:appdb:app:secret
*:*:*:postgres:localdevpassword

Help and discovery

\? lists meta-commands, \h lists SQL statements. \h <statement> is the fastest path to real syntax without leaving the shell.

Command	Shows
`\?`	Every psql meta-command
`\? variables`	The special psql variables (`ON_ERROR_STOP`, `AUTOCOMMIT`, ...)
`\h`	Every SQL statement psql knows
`\h CREATE INDEX`	Full grammar for one statement
`\copyright`	License text
`\conninfo`	Who and where you are connected as

Inspecting objects

The \d family is the schema browser. Add + for sizes, storage, and descriptions, add S to include system objects, and pass a pattern to filter.

Command	Lists
`\l` / `\l+`	Databases, with size and tablespace
`\c dbname`	Connect to another database (same session, new backend)
`\c dbname user`	Reconnect as a different role
`\dt`	Tables in `search_path`
`\dt public.*`	Tables in one schema
`\d orders`	Columns, indexes, constraints, triggers of one table
`\d+ orders`	Same plus storage, stats target, size, comments
`\dn` / `\dn+`	Schemas, with privileges
`\df`	Functions
`\df+ my_fn`	Function source and volatility
`\dv`	Views
`\dm`	Materialized views
`\di`	Indexes
`\ds`	Sequences
`\du` / `\dg`	Roles and their attributes
`\dp` / `\z`	Table, view, and sequence privileges (ACLs)
`\dx`	Installed extensions
`\dT+`	Types, including enum labels
`\dRp` / `\dRs`	Logical replication publications and subscriptions

Patterns accept * and ?, so \dt user* matches users and user_tokens.

Output, timing, and files

Wide rows are unreadable in aligned mode. \x auto switches to record-per-line formatting only when a row does not fit the terminal.

Command	Effect
`\x auto`	Expanded output when rows are too wide
`\timing on`	Print wall-clock duration after every statement
`\pset null '(null)'`	Make NULL visible instead of blank
`\pset pager off`	Stop piping into `less`
`\o out.txt`	Redirect all results to a file, `\o` alone stops
`\o \| gzip > out.gz`	Pipe results into a shell command
`\e`	Open the last query in `$EDITOR`, run it on save
`\e file.sql`	Edit a file, then execute it
`\i script.sql`	Run a file, paths relative to the shell cwd
`\ir script.sql`	Run a file relative to the including file
`\g out.txt`	Send just this one query to a file
`\watch 2`	Re-run the last query every 2 seconds
`\! ls`	Shell out
`\s`	Command history

\copy runs COPY but streams through the client, so it needs no server-side file permissions. This is the difference people trip over: COPY ... FROM '/tmp/x.csv' reads a file on the server, \copy reads one on your laptop.

\copy orders TO 'orders.csv' WITH (FORMAT csv, HEADER)
\copy orders (customer_id, total) FROM 'in.csv' WITH (FORMAT csv, HEADER)
\copy (SELECT id, email FROM customers) TO 'recent.csv' CSV HEADER

A \copy command must fit on one line. There is no backslash continuation for meta-commands, so a long query goes in a file or through COPY (…) TO STDOUT.

Variables and code generation

\set defines a client-side variable. Reference it as :name for a raw substitution, :'name' to get it quoted as a string literal, and :"name" quoted as an identifier.

\set target_email 'ada@example.com'
SELECT * FROM customers WHERE email = :'target_email';

\set tbl orders
SELECT count(*) FROM :"tbl";

\gexec runs the query, then executes every returned string as SQL. This is the idiomatic way to do a bulk DDL operation without writing a shell loop.

SELECT format('ANALYZE %I.%I;', schemaname, relname)
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
\gexec

Variable	Meaning
`ON_ERROR_STOP`	`1` aborts a script on the first error, the single most important setting
`AUTOCOMMIT`	`off` wraps every statement in an explicit transaction
`VERBOSITY`	`terse` / `default` / `verbose` for error detail
`SHOW_CONTEXT`	`errors` / `always` for the context lines under an error
`ECHO`	`queries` prints each statement as it runs, `all` echoes script lines
`ECHO_HIDDEN`	`on` prints the SQL behind `\d`-style commands
`HISTFILE`	Per-database history, `~/.psql_history-:DBNAME`

Scripting psql

For anything non-interactive, -X skips ~/.psqlrc so a colleague's settings cannot change your output, and -v ON_ERROR_STOP=1 makes failure an exit code instead of a warning buried in the log.

psql -X -q -c 'SELECT count(*) FROM orders' -d appdb
psql -X -v ON_ERROR_STOP=1 -f migrate.sql -d appdb
psql -X -q -A -t -F, -c 'SELECT id, email FROM customers' -d appdb > out.csv
psql -X -t -A -c 'SELECT pg_is_in_recovery()' -d appdb   # -> t or f

Flag	Purpose
`-c 'SQL'`	Run one command and exit
`-f file.sql`	Run a file and exit
`-A`	Unaligned output, no column padding
`-t`	Tuples only, drop the header and the row count footer
`-F,`	Field separator (with `-A`)
`-q`	Quiet, suppress `SELECT 1`-style notices
`-X`	Ignore `~/.psqlrc`
`-1`	Wrap the whole `-f` file in one transaction
`-v name=value`	Set a psql variable from the shell
`-P pager=off`	Set a `\pset` option from the command line
`--csv`	Proper CSV output, quoting included, better than `-A -F,`

Prefer --csv over -A -t -F, when values can contain commas or quotes.

A useful ~/.psqlrc, guarded so scripts are unaffected because they pass -X:

\set QUIET 1
\timing on
\x auto
\set ON_ERROR_STOP on
\pset null '(null)'
\set COMP_KEYWORD_CASE upper
\set HISTFILE ~/.psql_history-:DBNAME
\set PROMPT1 '%[%033[1;33m%]%n@%/%R%[%033[0m%]%# '
\unset QUIET