PostgreSQL Commands Cheat Sheet

Quick reference for PostgreSQL: psql client commands, DDL/DML statements, joins/aggregates, indexes, transactions and backup/restore — with common options and practical examples.

35 commands

Connection

psql -h <host> -p <port> -U <user> -d <db>

Open an interactive psql session against a remote or local database.

-h host; -p 5432 default; -U user; -d dbname; -W force password; -f file run script; -c one command

PGPASSWORD=secret psql -h db.example.com -U app -d app_production

psql -U <user> <db> --variable='ON_ERROR_STOP=1' -f <file.sql>

Run a SQL script against a database, stop on the first error.

psql -U app -d app -v ON_ERROR_STOP=1 -f migrations/0001_init.sql

psql Meta-Commands

\l[+] \c[onnect] [<db>]

List databases; then connect to one.

\l+ with size/tblsp info; \c db user change target

\l+; \c app_dev

\dt[+] [<pattern>]

List tables in the current schema matching a pattern (default: public).

\dt+ size+desc; \dt *.* all schemas; \d table describe a table

\dt+ public.*

\d <table|view|index|seq|matview>

Describe object structure: columns, indexes, FKs, comments.

\d orders

\dn / \du / \dv / \di

List schemas / roles / views / indexes respectively.

\du; \di+ idx_orders_user_id

\timing / \x / \? / \q

Toggle query timing; expanded display; show help; quit the session.

\timing on; \x auto; SELECT * FROM orders LIMIT 2; \q

\copy <table> FROM '<file>' DELIMITER ',' CSV HEADER

Bulk load data from a file (server-side, but uses client filesystem).

\copy orders(order_id,user_id,total,created_at) FROM '/tmp/orders.csv' WITH CSV HEADER

Database & Schema

CREATE DATABASE <db>

Create a new database with the template database options.

OWNER role; TEMPLATE template0; ENCODING 'UTF8'; LC_COLLATE

CREATE DATABASE app_dev OWNER app_user ENCODING 'UTF8' TEMPLATE template0

DROP DATABASE <db>

Drop a database (must not be in use; often done with IF EXISTS + force).

DROP DATABASE WITH (FORCE); postgres 13+

DROP DATABASE IF EXISTS app_old;

CREATE SCHEMA / DROP SCHEMA

Namespace for tables/views; part of the default search_path.

CREATE SCHEMA IF NOT EXISTS audit; DROP SCHEMA staging CASCADE;

Tables & DDL

CREATE TABLE

Define a new table with columns, types, defaults and constraints.

PRIMARY KEY; FOREIGN KEY ... REFERENCES; UNIQUE; CHECK; INHERITS; PARTITION BY RANGE|LIST

CREATE TABLE orders (id bigserial PRIMARY KEY, user_id int NOT NULL REFERENCES users(id), total numeric(10,2) NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now());

ALTER TABLE

Add/drop/rename columns, change types, add constraints, set storage params.

ADD COLUMN; DROP COLUMN; RENAME TO; ADD CONSTRAINT; ALTER COLUMN ... TYPE

ALTER TABLE orders ADD COLUMN currency char(3) NOT NULL DEFAULT 'USD', ADD CONSTRAINT chk_cur CHECK (currency IN ('USD','EUR','CNY'));

DROP TABLE / TRUNCATE

Remove a table (CASCADE if FK-referenced); TRUNCATE empties it fast.

DROP TABLE ... CASCADE; TRUNCATE ... RESTART IDENTITY CASCADE

TRUNCATE TABLE staging.events RESTART IDENTITY CASCADE;

COMMENT ON <table|column> IS '...'

Annotate database objects — surfaced by \d+ and IDE tooltips.

COMMENT ON COLUMN orders.total IS 'Subtotal before tax'

DML

INSERT INTO <table> [(cols)] VALUES (...) [RETURNING ...]

Insert one or more rows; RETURNING reveals columns of the inserted row.

INSERT ... SELECT; ON CONFLICT DO NOTHING / DO UPDATE (upsert); RETURNING

INSERT INTO orders(user_id, total) VALUES (42, 19.95) RETURNING id, created_at;

UPDATE ... SET ... [WHERE ...] [RETURNING ...]

Modify matching rows; always scope with WHERE unless intentional.

UPDATE ... FROM t2 WHERE ...; CTE (WITH ...) + UPDATE; ... RETURNING

UPDATE orders SET status='paid' WHERE user_id = 42 AND created_at < now() - interval '7 days' RETURNING id;

DELETE FROM ... [WHERE ...] [RETURNING ...]

Delete matching rows; TRUNCATE is faster when emptying whole tables.

DELETE ... USING t2 WHERE ...; RETURNING

DELETE FROM orders WHERE status='cancelled' AND created_at < now() - interval '30 days' RETURNING id;

Query & Filter

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT N OFFSET M

Read rows with filtering, ordering and pagination.

WHERE col op ANY/ALL(array); LIMIT n OFFSET n; FETCH FIRST n ROWS ONLY; FOR UPDATE/SHARE locks

SELECT id, status, total FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10 OFFSET 20;

DISTINCT / GROUP BY / HAVING

Eliminate duplicates, aggregate by columns, then filter on aggregates.

GROUP BY ROLLUP(a,b); GROUPING SETS; HAVING filter on aggregates; FILTER (WHERE ...) keep NULLs

SELECT user_id, count(*) FILTER (WHERE status='paid') AS paid, sum(total) AS gmv FROM orders GROUP BY user_id HAVING count(*) >= 3;

Joins

[INNER | LEFT | RIGHT | FULL] JOIN ... ON ...

Combine rows from multiple tables; LEFT JOIN keeps unmatched left rows.

JOIN LATERAL; NATURAL JOIN; USING(col) instead of ON; CROSS JOIN products

SELECT u.email, count(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.email;

WITH cte AS (...) SELECT ...

Materialize subqueries as CTEs; recursive CTEs for hierarchies/graphs.

WITH RECURSIVE tree AS (SELECT id, parent_id, name FROM cats WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, c.name FROM cats c JOIN tree t ON c.parent_id = t.id) SELECT * FROM tree;

Aggregate

count() / sum() / avg() / min() / max() / array_agg() / string_agg()

Built-in aggregate functions; great with FILTER clauses or GROUP BY.

DISTINCT inside count/sum; ORDER BY inside array_agg(string_agg)

SELECT date_trunc('day', created_at) AS day, sum(total) AS gmv FROM orders GROUP BY day ORDER BY day DESC LIMIT 7;

Index & View

CREATE INDEX ...

Accelerate WHERE/ORDER BY; partial indexes can target hot subsets.

CONCURRENTLY (no exclusive lock); INCLUDE (a,b) covering; USING btree|gin|gist|hash; partial WHERE

CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders(user_id, created_at DESC) INCLUDE (total);

EXPLAIN [ANALYZE] <query>

Show the query plan; ANALYZE executes and reports actual times & rows.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE); FORMAT JSON / TEXT / YAML

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

VACUUM / ANALYZE / REINDEX

Maintain storage and stats: reuse dead tuples, refresh planner stats, rebuild indexes.

VACUUM (FULL) table rewrite; ANALYZE update stats; REINDEX rebuild; autovacuum normally handles this

VACUUM (ANALYZE) orders;

CREATE VIEW / MATERIALIZED VIEW

Stored query; materialized view actually caches rows (refresh required).

CREATE OR REPLACE VIEW; MATERIALIZED VIEW ... WITH NO DATA; REFRESH MATERIALIZED VIEW CONCURRENTLY

CREATE MATERIALIZED VIEW mv_user_orders AS SELECT user_id, count(*) FROM orders GROUP BY user_id; CREATE UNIQUE INDEX ON mv_user_orders(user_id);

Transactions

BEGIN / COMMIT / ROLLBACK

Group statements into a single atomic unit of work (ACID).

BEGIN ISOLATION LEVEL SERIALIZABLE; COMMIT/ROLLBACK AND CHAIN; SAVEPOINT labels

BEGIN ISOLATION LEVEL REPEATABLE READ; UPDATE orders SET ... WHERE ...; ROLLBACK ON ERROR; COMMIT;

SELECT ... FOR UPDATE / FOR SHARE

Lock selected rows until the transaction ends; prevents concurrent updates.

FOR UPDATE / SHARE; NOWAIT or SKIP LOCKED for queues

SELECT id FROM jobs WHERE status='queued' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;

Backup & Restore

pg_dump <db> -Fc -f <file.dump>

Dump a database to a custom-format file (parallel restore, selective).

-Fc custom; -Ft tar; -Fp plain SQL; --schema=; --exclude-table=; -j jobs for -Fd

pg_dump -Fc -d app -f /backup/app-$(date +%F).dump

pg_restore -d <db> <file.dump>

Restore a custom/tar/dir dump; only the schema/data/roles needed.

--clean drop objects first; --if-exists; --schema=; --table=; --jobs=N; --no-owner; --single-transaction

pg_restore -d app_dev --clean --if-exists --jobs=4 /backup/app.dump

psql -d <db> -f <file.sql>

Restore a plain-SQL dump (output of pg_dump -Fp).

psql -U app -d app_new -v ON_ERROR_STOP=1 -f /backup/app.sql

User & Permission

CREATE ROLE / ALTER ROLE

Roles can log in (LOGIN) and own objects; grant attributes per role.

CREATE ROLE reporting LOGIN PASSWORD '...' NOSUPERUSER NOCREATEDB; ALTER ROLE app SET statement_timeout = '5s'

GRANT / REVOKE

Grant privileges on tables, schemas, databases, functions; column-level possible.

GRANT SELECT, INSERT ON TABLE ...; GRANT USAGE ON SCHEMA ...; WITH GRANT OPTION

GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;

\dt <schema>.* / pg_hba.conf

Tweak search_path and visible objects (\dt public.*); pg_hba.conf controls who can connect.

SHOW search_path; ALTER ROLE app IN DATABASE app SET search_path = app, public; pg_hba.conf hostssl all all 0.0.0.0/0 scram-sha-256

ALTER ROLE app SET search_path = app, public;

Cheatsheet version 1.0.0

Covers PostgreSQL 14+