PostgreSQL Cheat Sheet

Every PostgreSQL pattern you reach for daily, on one page: psql meta-commands, data types, string/date functions, JSONB and array operators, joins and CTEs, upserts, index types, and isolation levels. Bookmark it, or work through the underlying concepts in the free PostgreSQL tutorial.

psql meta-commands

psql meta-commands start with a backslash and are psql-specific — they are not SQL and won't work through a driver or ORM.

Command	Purpose
\l	list all databases
\c dbname	connect to a different database
\dt	list tables in the current schema
\d tablename	describe a table — columns, types, indexes, constraints
\dn	list schemas
\du	list roles/users
\df	list functions
\dv	list views
\x	toggle expanded (vertical) output — handy for wide rows
\timing	toggle showing each query's execution time
\e	open the last query in your editor
\i file.sql	execute a SQL file
\copy table TO 'file.csv' CSV	export a table to a client-side CSV file
\conninfo	show current connection details
\q	quit psql

Common data types

Type	Stores
integer / bigint / smallint	whole numbers of increasing range
numeric(p,s) / decimal(p,s)	exact fixed-point numbers
real / double precision	floating-point numbers
text / varchar(n) / char(n)	variable or fixed-length strings — text has no length limit
boolean	true / false / null
date / time / timestamp / timestamptz	date and time values, with or without a time zone
interval	a span of time, e.g. '3 days'
json / jsonb	JSON text / binary JSON (jsonb is indexable and usually preferred)
uuid	universally unique identifiers
type[]	arrays of any type, e.g. integer[], text[]

String functions

Function	Purpose	Example
a \|\| b	concatenation	first_name \|\| ' ' \|\| last_name
CONCAT(a, b, ...)	concatenation, NULL-safe	CONCAT('Hi ', name)
LENGTH(s)	character count	LENGTH('postgres') → 8
SUBSTRING(s FROM start FOR len)	extract a substring	SUBSTRING('postgres' FROM 1 FOR 4) → 'post'
UPPER(s) / LOWER(s)	case conversion	UPPER('sql') → 'SQL'
TRIM(s)	remove leading/trailing spaces	TRIM(' hi ') → 'hi'
REPLACE(s, old, new)	substring replacement	REPLACE('2026', '20', '19') → '1926'
POSITION(sub IN s)	1-based position of a substring	POSITION('gres' IN 'postgres') → 5

Date & time functions

Function	Purpose	Example
NOW() / CURRENT_TIMESTAMP	current date and time	SELECT NOW();
CURRENT_DATE	current date only	SELECT CURRENT_DATE;
AGE(d1, d2)	interval between two timestamps	AGE(NOW(), hire_date)
DATE_TRUNC('unit', ts)	truncate to year/month/day/hour...	DATE_TRUNC('month', order_date)
EXTRACT(field FROM ts)	pull out year/month/day/dow...	EXTRACT(YEAR FROM order_date)
TO_CHAR(ts, fmt)	format a date/timestamp as text	TO_CHAR(NOW(), 'YYYY-MM-DD')
ts + INTERVAL '1 day'	date/time arithmetic	NOW() + INTERVAL '7 days'

JSONB operators

jsonb stores JSON in a decomposed binary format — slightly slower to write than plain json, but faster to query and the only one that supports GIN indexing.

Operator	Returns	Example
->	value at a key, as jsonb	data -> 'address'
->>	value at a key, as text	data ->> 'name'
#>	value at a path, as jsonb	data #> '{address,city}'
#>>	value at a path, as text	data #>> '{address,city}'
@>	left contains right	data @> '{"active": true}'
<@	left is contained by right	'{"a":1}' <@ data
?	does the top-level key exist	data ? 'email'
?\| / ?&	any / all of these keys exist	data ?\| array['a','b']
\|\|	concatenate two jsonb values	data \|\| '{"new_key": 1}'

Array operators & functions

Syntax	Purpose	Example
ARRAY[1,2,3]	construct an array literal	SELECT ARRAY[1,2,3];
arr[1]	1-based element access	tags[1]
unnest(arr)	expand an array into rows	SELECT unnest(tags) FROM posts;
x = ANY(arr)	value matches any array element	WHERE 5 = ANY(scores)
arr @> ARRAY[x]	array contains an element/subset	WHERE tags @> ARRAY['sql']
arr1 && arr2	arrays overlap (share an element)	WHERE tags && ARRAY['sql','postgres']
array_agg(col)	aggregate rows into an array	SELECT array_agg(name) FROM users;
array_length(arr, 1)	length of the first dimension	array_length(tags, 1)

Joins & CTEs

LATERAL lets a subquery reference columns from a preceding table in the same FROM clause — useful for "top N per group" queries.

Pattern	Syntax
Inner join	FROM a JOIN b ON a.id = b.id
Left join	FROM a LEFT JOIN b ON a.id = b.id
Full outer join	FROM a FULL JOIN b ON a.id = b.id
Lateral join	FROM a, LATERAL (SELECT ... FROM b WHERE b.a_id = a.id LIMIT 3) sub
Common table expression	WITH recent AS (SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days') SELECT * FROM recent;
Recursive CTE	WITH RECURSIVE tree AS (base case UNION ALL recursive case) SELECT * FROM tree;

Window functions

Function	Purpose
ROW_NUMBER() OVER (...)	unique sequential number per row
RANK() / DENSE_RANK() OVER (...)	rank with / without gaps after ties
LAG(col, n) / LEAD(col, n) OVER (...)	value n rows before / after the current row
SUM/AVG/COUNT(...) OVER (PARTITION BY ...)	grouped aggregate without collapsing rows

Upsert — INSERT ... ON CONFLICT

PostgreSQL's native upsert: insert a row, or update it if a conflicting key already exists.

INSERT INTO users (id, email, login_count)
VALUES (1, 'a@example.com', 1)
ON CONFLICT (id)
DO UPDATE SET login_count = users.login_count + 1;

-- Or silently skip the conflicting row:
INSERT INTO users (id, email) VALUES (1, 'a@example.com')
ON CONFLICT (id) DO NOTHING;

Index types

Index	Best for
B-tree (default)	equality and range queries (=, <, >, BETWEEN, ORDER BY)
GIN	jsonb, arrays, and full-text search — indexes many values per row
GiST	geometric data, ranges, and full-text search
BRIN	very large tables with naturally sorted/sequential data (e.g. timestamps)
Hash	equality-only lookups; rarely chosen over B-tree today
Partial (WHERE clause)	indexing only a frequently-queried subset of rows
Expression	indexing the result of a function or expression, e.g. LOWER(email)

Transaction isolation levels

PostgreSQL has no distinct READ UNCOMMITTED — requesting it silently behaves as READ COMMITTED.

Level	Behaviour
READ COMMITTED (default)	each statement sees only data committed before it started
REPEATABLE READ	the whole transaction sees a consistent snapshot from its start
SERIALIZABLE	transactions behave as if run one at a time, fully isolated

VACUUM & ANALYZE

Routine maintenance that keeps MVCC and the query planner healthy.

VACUUM orders;            -- reclaim space from dead row versions
VACUUM ANALYZE orders;    -- also refresh planner statistics
VACUUM FULL orders;       -- rewrites the table, reclaims disk space (locks the table)
ANALYZE orders;           -- statistics only, no cleanup

Handy admin queries

Common one-liners for checking what the database is doing right now.

-- currently running queries
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active';

-- table size, human-readable
SELECT pg_size_pretty(pg_total_relation_size('orders'));

-- kill a stuck query
SELECT pg_cancel_backend(pid);   -- polite
SELECT pg_terminate_backend(pid); -- forceful

Practise what's on this page

Run any query on this page live against a real schema — free, no signup, results in under a second.

Open the SQL editor →

Frequently asked questions

Is this PostgreSQL cheat sheet free to use?

Yes. Every reference table here, the underlying tutorial, and the free SQL editor are completely free with no sign-up.

Are these psql commands or SQL commands?

The meta-commands section (starting with a backslash, like \dt) is psql-specific — a feature of the psql terminal client, not SQL. Everything else on the page is standard SQL or a PostgreSQL SQL extension.

Does jsonb replace json in PostgreSQL?

In almost all cases, yes — jsonb is the recommended type for storing JSON. It supports indexing (GIN) and faster queries; plain json only preserves exact input formatting and key order, which rarely matters.

Can I print or save this cheat sheet?

Yes — it's a normal web page, so your browser's print-to-PDF or bookmark feature works fine. There is no separate download required.

Where can I practise PostgreSQL queries?

The PostgreSQL tutorial covers every topic on this page in depth, and the free in-browser SQL editor lets you run queries live.