psql Meta-Commands Cheat Sheet for PostgreSQL
Chat2DB Teampsql is not just a way to type SQL. Its backslash commands — meta-commands — are a small language for exploring a database, formatting output, scripting and moving data around, and knowing thirty of them turns psql from a prompt into a genuinely fast tool.
Everything below runs on any modern PostgreSQL. The rule that unlocks half of them: adding + to a describe command shows more, and adding S includes system objects.
Exploring the database
\l List databases
\l+ ... with size, tablespace and description
\c dbname Connect to another database
\c dbname user host port
\dn List schemas
\dn+ ... with access privileges
\dt List tables in the search path
\dt+ ... with size and description
\dt *.* All tables in all schemas
\dt public.* All tables in one schema
\dt cust* Tables matching a pattern
\d orders Describe one table: columns, types, indexes, constraints, triggers
\d+ orders ... plus storage, stats target, and per-column description\d tablename is the command you will use most. It shows the column list, then indexes, check constraints, foreign keys, and — importantly — the foreign keys referencing this table, which is how you find what depends on it.
More object types:
\di Indexes \dv Views
\ds Sequences \dm Materialized views
\df Functions \dT Data types
\dx Installed extensions
\du Roles (users)
\dp Table access privileges
\dg Roles (same as \du)
\dD Domains
\dy Event triggers
\dF Text search configurations
\dRp Publications \dRs SubscriptionsPatterns work everywhere and use * and ? as wildcards, so \df pg_stat* lists the statistics functions and \dx on its own answers "what extensions does this database have?" faster than any catalog query.
To see the SQL behind an object:
\sf function_name Show a function's source
\sf+ function_name ... with line numbers
\sv view_name Show a view's definition
\ev view_name Edit a view definition in $EDITOROutput formatting
The defaults are aligned columns, which is unreadable for wide tables. These four commands cover almost every case:
\x Toggle expanded display (one column per line)
\x auto Expanded only when the row is too wide for the terminal
\pset null '(null)' Make NULLs visible instead of blank
\pset pager off Stop paging output through less\x auto is the single best quality-of-life setting in psql. Put it in your .psqlrc.
Other output modes:
\a Toggle aligned/unaligned output
\t Toggle showing only tuples (no headers or row count)
\f ',' Set the field separator (with \a, gives you CSV)
\H Toggle HTML output
\pset format csv Proper CSV output
\pset border 2 Draw full table bordersCombined, these produce script-friendly output:
psql -At -c "SELECT id FROM orders WHERE status = 'pending'" shop
# -A unaligned, -t tuples only: one bare id per line, ready to pipeTiming, history and repeating queries
\timing on Print execution time for every query
\watch 5 Re-run the last query every 5 seconds
\g Re-execute the last query
\gx Re-execute and display expanded
\gset prefix Store the result row into variables
\e Open the last query in $EDITOR
\p Print the current query buffer
\r Reset (clear) the query buffer
\s Show command history
\s filename Save history to a file\watch turns psql into a monitoring tool in one keystroke:
SELECT count(*) FILTER (WHERE state = 'active') AS active,
count(*) FILTER (WHERE state = 'idle in transaction') AS idle_txn,
count(*) AS total
FROM pg_stat_activity;
\watch 2Press Ctrl-C to stop. This is the fastest way to watch a long-running migration, a vacuum, or connection growth during an incident.
Moving data in and out
\copy is a client-side command that reads and writes files on your machine, using your permissions. COPY (no backslash) is a server-side SQL command that needs superuser or the pg_read_server_files role and reads files on the server. On a managed service you will almost always want \copy.
\copy orders TO 'orders.csv' WITH (FORMAT csv, HEADER)
\copy orders FROM 'orders.csv' WITH (FORMAT csv, HEADER)
\copy (SELECT id, total FROM orders WHERE created_at > '2026-01-01') TO 'recent.csv' CSV HEADERThe query form is the useful one — you can export exactly the rows you want without creating a temporary table.
Redirecting output to a file is separate:
\o results.txt Send all subsequent query output to a file
\o Back to the screen
\o | grep ERROR Pipe output to a shell commandRunning scripts
\i script.sql Execute a file
\ir script.sql Execute a file relative to the current script's directory
\! ls -la Run a shell command
\cd /some/path Change psql's working directory\ir matters for any project with a directory of migration files: it resolves paths relative to the including script, so the same files work no matter where psql was started.
For non-interactive runs, two flags belong on almost every invocation:
psql --set ON_ERROR_STOP=on --single-transaction -f migration.sql shopWithout ON_ERROR_STOP, psql reports errors and keeps going — a migration that half-applied will exit 0.
Variables
\set name value Define a variable
\set List all variables
\unset name Remove one
:name Interpolate (raw)
:'name' Interpolate as a quoted string literal
:"name" Interpolate as a quoted identifier\set target_email 'alice@example.com'
SELECT * FROM users WHERE email = :'target_email';
\set tbl orders
SELECT count(*) FROM :"tbl";Variables can also be set from the command line, which makes scripts parameterisable:
psql -v cutoff="'2026-01-01'" -f report.sql shopAnd \gset captures results into variables, which is how you write a script that branches on data:
SELECT count(*) AS pending_count FROM orders WHERE status = 'pending' \gset
\echo Pending orders: :pending_countConditionals in scripts
psql has \if, which is genuinely useful in migration scripts:
SELECT EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_name = 'orders' AND column_name = 'discount_cents'
) AS has_column \gset
\if :has_column
\echo 'Column already present, skipping'
\else
ALTER TABLE orders ADD COLUMN discount_cents integer NOT NULL DEFAULT 0;
\endifConnection information
\conninfo Show the current connection details
\encoding Show or set client encoding
\password username Change a password without it appearing in history or logs\password is worth calling out: it hashes the password on the client and sends the hash, so the plaintext never appears in the server log or your shell history. Use it instead of ALTER USER ... PASSWORD '...'.
A .psqlrc worth having
Put this in ~/.psqlrc:
\set QUIET 1
\x auto
\timing on
\pset null '(null)'
\pset linestyle unicode
\pset border 2
\set COMP_KEYWORD_CASE upper
\set HISTSIZE 10000
\set HISTFILE ~/.psql_history-:DBNAME
\set VERBOSITY verbose
\set PROMPT1 '%[%033[1;32m%]%n@%/%[%033[0m%]%R%# '
-- Handy shortcuts
\set activity 'SELECT pid, now() - query_start AS runtime, state, wait_event_type, left(query,60) AS query FROM pg_stat_activity WHERE state <> ''idle'' ORDER BY runtime DESC;'
\set locks 'SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, left(blocked.query,50) AS blocked_query FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid)) WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;'
\set bloat 'SELECT relname, n_live_tup, n_dead_tup, round(n_dead_tup*100.0/nullif(n_live_tup+n_dead_tup,0),1) AS dead_pct FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY dead_pct DESC LIMIT 20;'
\unset QUIETThen :activity, :locks and :bloat are one word each at the prompt. The per-database history file is the underrated line — history from your production session no longer bleeds into your local one.
\set VERBOSITY verbose makes errors include the SQLSTATE code and the source location, which turns "syntax error" into something you can search for.
Getting help without leaving the prompt
\? All meta-commands
\h List all SQL commands
\h ALTER TABLE Syntax for one command, straight from the docs
\q Quit\h ALTER TABLE is faster than opening a browser and is always the version you are actually connected to.
When psql is the wrong tool
psql is unbeatable for scripting, for servers you reach over SSH, and for anything you want to automate. It is a poor fit for exploring a schema you do not know, comparing two databases side by side, or reading a result set with forty columns and a JSON blob in one of them. For that work a GUI is simply better — Chat2DB (opens in a new tab) keeps multiple connections open at once and will write the query from a plain-English description against your actual schema, which is a real time-saver on an unfamiliar database. Download it at chat2db.ai/download (opens in a new tab).
Most people end up using both: the GUI to understand a database, psql to automate what they learned.