$ dbaguide.com / cheat-sheets / postgresql

psql CLI Quick Reference

Complete PostgreSQL Command Line Reference

v16 / v17 compatible

Updated: October 2026 · Production-grade

Connection Commands

Command	Description
psql -U postgres -d mydb	Connect with specific user and database
psql -h 192.168.1.10 -p 5432 -U app_user -d prod_db ↳ Connect to remote server
psql "postgresql://user:pass@host:5432/dbname" ↳ Connection string (URI) format
psql -U postgres -d mydb -c "SELECT version();" ↳ Run single command and exit
psql -U postgres -f /path/to/script.sql ↳ Execute SQL file
PGPASSWORD=secret psql -U user -d db	Pass password via environment variable
psql -U postgres -d mydb -q	Quiet mode (suppress messages)

.pgpass file format: hostname:port:database:username:password — chmod 600 required.

Variables & Settings

Command	Description
\set VAR value	Define a variable
\unset VAR	Delete a variable
\set	List all variables
SELECT :'VAR';	Use variable in query
\echo :VAR	Print variable value
\setenv PGTZ 'UTC'	Set environment variable

Example Usage

\set schema public SELECT * FROM :'schema'.users LIMIT 10;

Help & Useful Commands

\?	List all meta commands
\h SELECT	SQL command syntax help
\gx	Execute last query in expanded mode
\watch 5	Repeat last query every 5 seconds
\errverbose	Show last error in full detail

Meta Commands (Backslash Commands)

Database & Connection

\l or \list	List all databases
\c mydb	Switch database
\conninfo	Show active connection info
\q	Quit psql

Tables & Schemas

\dt	List tables in current schema
\dt public.*	All tables in public schema
\d tablename	Show table structure
\d+ tablename	Detailed table info (including size)
\dn	List schemas
\dn+	Schemas + permissions
\dv	List views
\dm	List materialized views
\di	List indexes
\df	List functions
\ds	List sequences

Users & Privileges

\du	List roles and users
\dp tablename	Show table privileges
\z tablename	\dp Same as \dp

Appending + to any meta command (e.g. \dt+, \df+) displays size, description, and owner details. \? lists all meta commands.

.psqlrc Sample Configuration

-- ~/.psqlrc (auto-loaded on every psql session) \set QUIET 1 \timing on \x auto \set PROMPT1 '%[%033[1;32m%]%n@%/%[%033[0m%]# ' \set HISTSIZE 10000 \set COMP_KEYWORD_CASE upper \unset QUIET

Output & Format Settings

Command	Description
\x	Toggle expanded (vertical) mode
\x auto	Automatic expanded mode
\a	Toggle aligned / unaligned mode
\t	Show rows only (tuple only mode)
\timing	Measure and display query duration
\pset border 2	Set border thickness
\pset format csv	CSV format output
\o /tmp/output.txt	Redirect output to file
\o	Stop redirecting to file
\H	Toggle HTML output mode

Query & Script Management

Command	Description
\e	Edit query in external editor ($EDITOR)
\ef function_name	Edit function definition in editor
\ev view_name	Edit view definition in editor
\g	Re-execute last query
\g /tmp/result.csv	Save last query result to file
\i /path/to/file.sql	Execute SQL file
\ir relative/path.sql	Execute SQL file at relative path
\s	Show command history
\s /tmp/history.sql	Save history to file

COPY & Data Import/Export

Command	Description
\copy table TO '/tmp/out.csv' CSV HEADER ↳ Export table to CSV (client-side)
\copy table FROM '/tmp/in.csv' CSV HEADER ↳ Import CSV into table
\copy (SELECT ...) TO '/tmp/q.csv' CSV HEADER ↳ Export query result to CSV
\copy table TO STDOUT	Export to standard output

\copy is client-side; COPY is server-side. File permissions differ.

Debugging & Diagnostic Queries

pg_stat_activity · pg_stat_user_tables · pg_locks

Active Connections

-- Active connections SELECT pid, usename, application_name, state, query FROM pg_stat_activity WHERE state != 'idle';

Table Sizes

-- Table sizes SELECT relname, pg_size_pretty( pg_total_relation_size(relid)) FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC;

Long-Running Queries

-- Long-running queries SELECT pid, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '5 min';

Lock Waiters

-- Lock waiters SELECT pid, relation::regclass, mode, granted FROM pg_locks WHERE NOT granted;