Menu
CoddyTech

PostgreSQL Cheat Sheet

Last updated

psql commands

Meta-commands run inside psql (the PostgreSQL terminal client), not on the server. They start with a backslash and need no semicolon.

What it doesCommand
List databases\l
Connect to another database\c shop
List tables\dt
List tables in every schema\dt *.*
Describe a table (columns, indexes, keys)\d users
Describe with storage and comments\d+ users
List schemas\dn
List views\dv
List indexes\di
List functions\df
List roles (users)\du
Show the current connection\conninfo
Run SQL from a file\i setup.sql
Toggle query timing\timing
Expanded output (one column per line)\x
Edit the last query in your editor\e
Help on SQL syntax\h CREATE TABLE
List all meta-commands\?
Quit psql\q

Connecting from the terminal

What it doesCommand
Connect as a user to a databasepsql -U postgres -d shop
Connect to a remote host and portpsql -h db.example.com -p 5432 -U app -d shop
Connect with a connection URLpsql postgresql://app:secret@localhost:5432/shop
Run one query and exitpsql -d shop -c "SELECT count(*) FROM users;"
Run a SQL filepsql -d shop -f setup.sql
Create a database from the shellcreatedb shop
Drop a database from the shelldropdb shop

Databases and schemas

What it doesSQL
Create a databaseCREATE DATABASE shop;
Drop a databaseDROP DATABASE shop;
Show the current databaseSELECT current_database();
Create a schemaCREATE SCHEMA sales;
Show the schema search orderSHOW search_path;
Look in a schema firstSET search_path TO sales, public;
Show the server versionSELECT version();

Tables and data types

What it doesSQL
Create a table with an identity keyCREATE TABLE products (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, price numeric(10,2) CHECK (price >= 0), created_at timestamptz DEFAULT now());
Add a columnALTER TABLE users ADD COLUMN phone text;
Rename a columnALTER TABLE users RENAME COLUMN country TO country_code;
Change a column's typeALTER TABLE users ALTER COLUMN age TYPE smallint;
Set a defaultALTER TABLE users ALTER COLUMN active SET DEFAULT false;
Drop a columnALTER TABLE users DROP COLUMN tags;
Empty a table and reset its identityTRUNCATE orders, users RESTART IDENTITY;
Drop a table if it existsDROP TABLE IF EXISTS products;
Exact decimal for moneynumeric(10,2)
Date and time with time zonetimestamptz
Random UUIDSELECT gen_random_uuid();
Enum typeCREATE TYPE mood AS ENUM ('happy', 'sad');

Constraints and keys

What it doesSQL
Add a primary keyALTER TABLE logs ADD PRIMARY KEY (id);
Foreign key that deletes childrenCREATE TABLE reviews (id integer PRIMARY KEY, user_id integer REFERENCES users(id) ON DELETE CASCADE);
Add a check constraintALTER TABLE orders ADD CONSTRAINT amount_positive CHECK (amount > 0);
Unique where NULLs count as equal (15+)CREATE TABLE emails (address text UNIQUE NULLS NOT DISTINCT);
Make a column requiredALTER TABLE users ALTER COLUMN country SET NOT NULL;
Drop a constraintALTER TABLE users DROP CONSTRAINT users_email_key;

Querying (PostgreSQL extras)

Standard SELECT, WHERE and JOIN are on the SQL cheat sheet; these are the PostgreSQL forms.

What it doesSQL
Case-insensitive matchSELECT * FROM users WHERE name ILIKE 'a%';
Regular expression matchSELECT * FROM users WHERE email ~ '@example\.com$';
First row per groupSELECT DISTINCT ON (user_id) user_id, amount FROM orders ORDER BY user_id, amount DESC;
Limit and offsetSELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
Standard row limitSELECT * FROM users ORDER BY id FETCH FIRST 5 ROWS ONLY;
Cast with ::SELECT '42'::integer + 1;
NULL fallbackSELECT name, COALESCE(email, 'none') FROM users;
Compare treating NULLs as equalSELECT * FROM users WHERE email IS DISTINCT FROM 'asha@example.com';
Generate a series of numbersSELECT * FROM generate_series(1, 5);
Aggregate into a stringSELECT string_agg(name, ', ' ORDER BY name) FROM users;
Conditional aggregateSELECT count(*) FILTER (WHERE status = 'paid') AS paid, count(*) AS total FROM orders;

Insert, update, delete and upsert

What it doesSQL
Insert and get the new id backINSERT INTO users (name, email) VALUES ('Dana', 'dana@example.com') RETURNING id;
Insert several rowsINSERT INTO users (name) VALUES ('Eli'), ('Femi');
Upsert (insert or update)INSERT INTO users (name, email) VALUES ('Asha K', 'asha@example.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
Insert, ignore duplicatesINSERT INTO users (name, email) VALUES ('Ben', 'ben@example.com') ON CONFLICT DO NOTHING;
Update and see the changed rowsUPDATE employees SET salary = salary * 1.1 WHERE department = 'IT' RETURNING name, salary;
Update from another tableUPDATE orders SET status = 'vip' FROM users WHERE orders.user_id = users.id AND users.country = 'IN';
Delete and see what was deletedDELETE FROM orders WHERE status = 'refunded' RETURNING id;
Delete using another tableDELETE FROM orders USING users WHERE orders.user_id = users.id AND users.active = false;
Copy rows into a new tableCREATE TABLE paid_orders AS SELECT * FROM orders WHERE status = 'paid';

JSONB

What it doesSQL
Get a field as JSONSELECT profile -> 'plan' FROM users;
Get a field as textSELECT profile ->> 'plan' FROM users;
Get a nested field as textSELECT profile #>> '{address,city}' FROM users;
Rows whose JSON contains a valueSELECT * FROM users WHERE profile @> '{"plan": "pro"}';
Rows that have a keySELECT * FROM users WHERE profile ? 'city';
Set a keyUPDATE users SET profile = jsonb_set(profile, '{plan}', '"team"') WHERE id = 2;
Merge objectsUPDATE users SET profile = profile || '{"beta": true}';
Remove a keyUPDATE users SET profile = profile - 'city';
Build JSON from columnsSELECT jsonb_build_object('id', id, 'name', name) FROM users;
Rows as a JSON arraySELECT jsonb_agg(users) FROM users;
Index for @> and ? lookupsCREATE INDEX users_profile_idx ON users USING gin (profile);

Arrays

What it doesSQL
Array literalSELECT ARRAY[1, 2, 3];
Rows where the array has a valueSELECT * FROM users WHERE 'sql' = ANY(tags);
Rows where the array contains all ofSELECT * FROM users WHERE tags @> ARRAY['sql', 'python'];
One row per elementSELECT name, unnest(tags) FROM users;
Append an elementUPDATE users SET tags = array_append(tags, 'sql') WHERE id = 2;
Array lengthSELECT name, cardinality(tags) FROM users;

Dates and times

What it doesSQL
Now, with time zoneSELECT now();
TodaySELECT current_date;
Add an intervalSELECT current_date + INTERVAL '7 days';
Rows from the last 30 daysSELECT * FROM orders WHERE created_at > now() - INTERVAL '30 days';
Truncate to the monthSELECT date_trunc('month', created_at) AS month, sum(amount) FROM orders GROUP BY 1 ORDER BY 1;
Extract a partSELECT EXTRACT(YEAR FROM hired_on) FROM employees;
Difference between dates (days)SELECT DATE '2026-12-25' - DATE '2026-11-17';
Age between datesSELECT name, age(current_date, hired_on) FROM employees;
Format a date as textSELECT to_char(hired_on, 'DD Mon YYYY') FROM employees;
Parse text into a dateSELECT to_date('17/11/2026', 'DD/MM/YYYY');
Convert to a time zoneSELECT now() AT TIME ZONE 'Asia/Kolkata';

Text functions

What it doesSQL
Concatenate (NULL makes NULL)SELECT name || ' <' || email || '>' FROM users;
Concatenate, skipping NULLsSELECT concat_ws(' ', name, email) FROM users;
Upper and lower caseSELECT upper(name), lower(name) FROM users;
LengthSELECT name, length(name) FROM users;
Part of a stringSELECT substring(name FROM 1 FOR 2) FROM users;
ReplaceSELECT replace(email, '@example.com', '') FROM users;
Split a partSELECT split_part(email, '@', 2) FROM users;
Regular expression replaceSELECT regexp_replace('a1b22c', '[0-9]+', '', 'g');
Trim spacesSELECT trim(' hi ');

CTEs and window functions

What it doesSQL
Name a subquery with WITHWITH paid AS (SELECT user_id, sum(amount) AS total FROM orders WHERE status = 'paid' GROUP BY user_id) SELECT * FROM paid WHERE total > 50;
Recursive CTE (count to 5)WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5) SELECT i FROM n;
Number rows per groupSELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees;
Rank with tiesSELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees;
Running totalSELECT id, amount, sum(amount) OVER (ORDER BY id) FROM orders;
Previous row's valueSELECT name, salary, LAG(salary) OVER (ORDER BY hired_on) FROM employees;
Join to each row's own subquerySELECT u.name, o.amount FROM users u CROSS JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 1) o;

Indexes

What it doesSQL
B-tree index (the default)CREATE INDEX orders_user_idx ON orders (user_id);
Unique indexCREATE UNIQUE INDEX employees_name_idx ON employees (name);
Multicolumn indexCREATE INDEX orders_user_created_idx ON orders (user_id, created_at);
Partial indexCREATE INDEX orders_paid_idx ON orders (created_at) WHERE status = 'paid';
Expression indexCREATE INDEX users_lower_email_idx ON users (lower(email));
Covering index (index-only scans)CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (amount);
Build without blocking writesCREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
Drop an indexDROP INDEX IF EXISTS orders_user_idx;
See the planEXPLAIN SELECT * FROM orders WHERE user_id = 1;
See the plan with real timingsEXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

Transactions and locks

What it doesSQL
Start, then keep or undoBEGIN; UPDATE employees SET salary = 0 WHERE id = 3; ROLLBACK;
Undo part of a transactionBEGIN; INSERT INTO users (name) VALUES ('Gil'); SAVEPOINT s1; DELETE FROM users WHERE name = 'Gil'; ROLLBACK TO s1; COMMIT;
Stricter isolationBEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM orders; COMMIT;
Lock the rows you readBEGIN; SELECT * FROM orders WHERE id = 1 FOR UPDATE; COMMIT;
Job queue: skip locked rowsBEGIN; SELECT id FROM orders WHERE status = 'paid' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; COMMIT;

Users, roles and privileges

What it doesSQL
Create a login roleCREATE ROLE app LOGIN PASSWORD 'change-me';
Read-only access to a tableGRANT SELECT ON users TO app;
Read access to every table in a schemaGRANT SELECT ON ALL TABLES IN SCHEMA public TO app;
Take a privilege backREVOKE SELECT ON users FROM app;
Change a passwordALTER ROLE app PASSWORD 'new-secret';
Show the current userSELECT current_user;

Maintenance and monitoring

What it doesSQL
Running queriesSELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle';
Cancel a querySELECT pg_cancel_backend(12345);
End a connectionSELECT pg_terminate_backend(12345);
Size of a table with its indexesSELECT pg_size_pretty(pg_total_relation_size('orders'));
Size of the databaseSELECT pg_size_pretty(pg_database_size(current_database()));
Update planner statisticsANALYZE orders;
Reclaim dead rowsVACUUM orders;
Rewrite a table to its minimum size (locks it)VACUUM FULL orders;
Show a settingSHOW work_mem;
Change a setting for this sessionSET work_mem = '64MB';

Backup and restore

Shell commands from the PostgreSQL client tools.

What it doesCommand
Dump a database to a SQL filepg_dump -U postgres shop > shop.sql
Dump in the custom formatpg_dump -Fc -U postgres shop > shop.dump
Dump one tablepg_dump -t orders shop > orders.sql
Restore a SQL dumppsql -d shop -f shop.sql
Restore a custom-format dumppg_restore -d shop shop.dump
Dump every database and rolepg_dumpall > cluster.sql
Export a query to CSV (from psql)\copy (SELECT * FROM orders) TO 'orders.csv' CSV HEADER
Import a CSV (from psql)\copy orders (user_id, amount, status) FROM 'orders.csv' CSV HEADER

Every psql command and PostgreSQL statement you reach for, in copy-paste tables: connecting and the \d commands first, then tables, upserts, JSONB, dates, window functions, indexes, roles, backups and maintenance.

The SQL here is PostgreSQL 17 syntax, and most of it works in earlier versions too. The SQL statements were checked on PostgreSQL 17; to try one, paste it into the PostgreSQL playground, which comes with sample tables.

PostgreSQL cheat sheet FAQ

What are psql commands?
psql is PostgreSQL's terminal client. Besides SQL, it understands meta-commands that start with a backslash: \l lists databases, \c connects to one, \dt lists tables, \d users describes a table and \q quits. They run in psql itself, so they need no semicolon and do not work in other clients.
How do I list all tables in PostgreSQL?
In psql, type \dt (or \dt *.* for every schema). In SQL, from any client: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
Is PostgreSQL SQL different from MySQL?
The core (SELECT, JOIN, GROUP BY) is the same. PostgreSQL adds ILIKE, :: casts, RETURNING, DISTINCT ON, ON CONFLICT upserts, arrays and JSONB, and uses identity columns where MySQL uses AUTO_INCREMENT.
What is the difference between JSON and JSONB?
json stores the text as written; jsonb stores a parsed binary form, drops duplicate keys and whitespace, and supports indexes (GIN) and operators like @> and ?. Use jsonb unless you need the exact original text.
Can I practice these queries online?
Yes. The PostgreSQL playground runs any query on a real PostgreSQL 17 database in your browser, with sample tables you can edit. Statements that need server privileges, like CREATE DATABASE or CREATE ROLE, need your own PostgreSQL.
Is this PostgreSQL cheat sheet free?

Yes. It is free to read, copy and print, with no sign-up.

Coddy programming languages illustration

Learn PostgreSQL with Coddy

Get started