PostgreSQL Cheatsheet

psql CLI

psql -U user -d dbnameConnect to database as user

psql -h host -p 5432 -U user -d dbConnect to remote host on specific port

\lList all databases

\c dbnameSwitch to / connect to database

\dtList tables in current schema

\d tablenameDescribe table (columns, types, indexes)

\d+ tablenameDetailed table description with storage info

\diList indexes

\dvList views

\dfList functions

\duList roles / users

\dnList schemas

\timingToggle query execution time display

\xToggle expanded output mode (one column per line)

\eOpen last query in $EDITOR

\i file.sqlExecute SQL from file

\o file.txtSend output to file (empty \o to stop)

\copy t FROM file.csv CSV HEADERClient-side COPY from local file

\qQuit psql

\?List all psql backslash commands

\h SELECTShow SQL syntax help for SELECT

Data Types

INTEGER / BIGINT / SMALLINT4-byte / 8-byte / 2-byte signed integer

SERIAL / BIGSERIALAuto-incrementing integer (creates sequence)

col BIGINT GENERATED ALWAYS AS IDENTITYSQL-standard auto-increment (preferred over SERIAL in PG 10+)

NUMERIC(p, s) / DECIMALExact decimal, p total digits, s decimal places

REAL / DOUBLE PRECISION4-byte / 8-byte floating-point number

VARCHAR(n) / TEXT / CHAR(n)Variable-length / unlimited text / fixed-length

BOOLEANTRUE / FALSE / NULL

DATECalendar date (no time zone)

TIMETime of day (no date)

TIMESTAMPDate and time without time zone

TIMESTAMPTZDate and time with time zone (recommended)

INTERVALTime span, e.g. INTERVAL '1 day 2 hours'

UUID128-bit universally unique identifier

JSON / JSONBJSON text / binary JSON (indexed, faster queries)

ARRAYArray of any base type, e.g. INTEGER[]

BYTEABinary data (byte array)

INET / CIDR / MACADDRIP address / IP network / MAC address

CREATE TYPE mood AS ENUM ('happy', 'sad')Custom enum type

TSVECTOR / TSQUERYFull-text search document / query

DDL - Schema & Tables

CREATE DATABASE nameCreate a new database

DROP DATABASE nameDrop database (cannot be undone)

CREATE TABLE t (id SERIAL PRIMARY KEY, name TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW())Create table with common column patterns

ALTER TABLE t ADD COLUMN col TYPEAdd a column

ALTER TABLE t DROP COLUMN colRemove a column

ALTER TABLE t ALTER COLUMN col TYPE newtype USING col::newtypeChange column type with cast

ALTER TABLE t RENAME COLUMN old TO newRename a column

ALTER TABLE t RENAME TO new_nameRename a table

ALTER TABLE t ADD CONSTRAINT name UNIQUE (col)Add unique constraint

ALTER TABLE t ADD FOREIGN KEY (col) REFERENCES other(id) ON DELETE CASCADEAdd foreign key with cascade delete

ALTER TABLE t ADD CHECK (col > 0)Add check constraint

DROP TABLE tDrop table

DROP TABLE IF EXISTS t CASCADEDrop table and dependent objects if exists

TRUNCATE TABLE t RESTART IDENTITY CASCADERemove all rows, reset sequences, cascade

CREATE VIEW v AS SELECT ...Create a view

CREATE OR REPLACE VIEW v AS SELECT ...Create or replace a view

CREATE MATERIALIZED VIEW mv AS SELECT ...Create materialized view (cached result)

REFRESH MATERIALIZED VIEW mvRefresh materialized view data

REFRESH MATERIALIZED VIEW CONCURRENTLY mvRefresh without locking reads (requires unique index)

CREATE SCHEMA nameCreate a schema namespace

Queries & Filtering

SELECT * FROM tSelect all columns

SELECT col1, col2 FROM tSelect specific columns

SELECT DISTINCT col FROM tUnique values only

WHERE col = valEquality filter

WHERE col != val -- or <>Not equal

AND / OR / NOTLogical operators

IN (1, 2, 3)Match any value in list

NOT IN (...)Exclude values in list

BETWEEN x AND yInclusive range filter

LIKE '%pat%'Pattern match (case-sensitive, % = wildcard)

ILIKE '%pat%'Case-insensitive pattern match (PostgreSQL-specific)

IS NULL / IS NOT NULLNULL check

LIMIT n OFFSET mReturn n rows starting at row m

ORDER BY col ASC / DESCSort results

ORDER BY col NULLS FIRST / NULLS LASTControl NULL sort position

FETCH FIRST n ROWS ONLYSQL-standard alternative to LIMIT

SELECT ... FOR UPDATELock selected rows for update

CASE WHEN cond THEN val ELSE other ENDConditional expression

COALESCE(a, b, c)Return first non-NULL value

NULLIF(a, b)Return NULL if a = b, else return a

Joins

INNER JOIN t2 ON t1.id = t2.fkOnly matching rows from both tables

LEFT JOIN t2 ON t1.id = t2.fkAll rows from t1, matching rows from t2 (NULL if no match)

RIGHT JOIN t2 ON t1.id = t2.fkAll rows from t2, matching from t1

FULL OUTER JOIN t2 ON t1.id = t2.fkAll rows from both tables, NULLs where no match

CROSS JOIN t2Cartesian product - every row combined with every row

JOIN t2 USING (id)Join on identically named column, deduplicated in output

JOIN LATERAL (SELECT ...) AS sub ON trueSubquery that can reference columns from preceding FROM items

JOIN t AS t2 ON t.parent_id = t2.idSelf-join (hierarchical data)

NATURAL JOIN t2Auto-join on all same-named columns (use with care)

Aggregation

COUNT(*)Count all rows including NULLs

COUNT(col)Count non-NULL values in col

COUNT(DISTINCT col)Count distinct non-NULL values

SUM(col)Sum of non-NULL values

AVG(col)Average of non-NULL values

MIN(col) / MAX(col)Minimum / maximum value

GROUP BY colGroup rows by column value

HAVING COUNT(*) > 1Filter groups (like WHERE but for aggregates)

COUNT(*) FILTER (WHERE status = 'active')Conditional aggregate - count only rows matching the condition

STRING_AGG(col, ', ')Concatenate values into string with separator

STRING_AGG(col, ', ' ORDER BY col)Concatenate with explicit order

ARRAY_AGG(col)Aggregate values into an array

JSON_AGG(row)Aggregate rows into a JSON array

JSONB_AGG(row)Aggregate rows into a JSONB array

BOOL_AND(col) / BOOL_OR(col)True if all / any values are true

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col)Median (continuous percentile)

JSON & JSONB

col->>'key'Get object field as text

col->'key'Get object field as JSON

col#>>'{a,b}'Get nested path value as text

col#>'{a,b}'Get nested path value as JSON

jsonb_build_object('k', v, 'k2', v2)Build JSONB object from key-value pairs

jsonb_build_array(1, 2, 3)Build JSONB array

jsonb_set(col, '{key}', '"value"')Set a key in existing JSONB

col || '{"k":"v"}'::jsonbMerge two JSONB objects

col - 'key'Remove key from JSONB object

col ? 'key'Does key exist in JSONB?

col ?| ARRAY['a','b']Any of the keys exist?

col ?& ARRAY['a','b']All of the keys exist?

col @> '{"k":"v"}'::jsonbJSONB contains this value?

col <@ '{"k":"v"}'::jsonbJSONB is contained by this value?

jsonb_each(col)Expand JSONB object to (key, value) rows

jsonb_object_keys(col)Return set of top-level keys

jsonb_array_elements(col)Expand JSONB array to individual elements

jsonb_array_length(col)Number of elements in JSONB array

to_jsonb(val)Convert any SQL value to JSONB

row_to_json(t)Convert a row to JSON object

CREATE INDEX ON t USING GIN (col)GIN index for fast JSONB queries

CREATE INDEX ON t USING GIN (col jsonb_path_ops)GIN index optimised for @> operator

Arrays

col = ARRAY[1, 2, 3]Array literal

col[1]First element (1-indexed)

col[2:4]Array slice elements 2 through 4

array_length(col, 1)Length of array along dimension 1

cardinality(col)Total number of elements (any dimension)

array_append(col, val)Append element to end

array_prepend(val, col)Prepend element to beginning

array_remove(col, val)Remove all occurrences of val

array_replace(col, old, new)Replace all occurrences of old with new

array_cat(a, b)Concatenate two arrays

array_to_string(col, ',')Join array elements into string

string_to_array('a,b,c', ',')Split string into array

unnest(col)Expand array to a set of rows

col @> ARRAY[1, 2]Array contains these elements?

col && ARRAY[1, 2]Arrays overlap (share any element)?

ANY(col) = valValue equals any element in array

ALL(col) > 0Condition holds for all elements

CREATE INDEX ON t USING GIN (col)GIN index for fast array containment queries

Window Functions

ROW_NUMBER() OVER (ORDER BY col)Unique sequential number per row in partition

RANK() OVER (ORDER BY col)Rank with gaps for ties (1,1,3...)

DENSE_RANK() OVER (ORDER BY col)Rank without gaps for ties (1,1,2...)

NTILE(4) OVER (ORDER BY col)Divide rows into n buckets (quartiles)

LAG(col, 1) OVER (ORDER BY col)Value from previous row (offset 1)

LEAD(col, 1) OVER (ORDER BY col)Value from next row (offset 1)

FIRST_VALUE(col) OVER (...)First value in the window frame

LAST_VALUE(col) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)Last value in partition - must extend frame to partition end (default frame stops at current row)

NTH_VALUE(col, 2) OVER (...)Nth value in the window frame

SUM(col) OVER (ORDER BY date)Running total (cumulative sum)

AVG(col) OVER (ORDER BY date ROWS 6 PRECEDING)7-row moving average (current row + 6 preceding rows)

PARTITION BY col ORDER BY colReset window for each partition value

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWFrame from start of partition to current row

WINDOW w AS (PARTITION BY col ORDER BY col)Named window definition, reuse with OVER w

CTEs & Subqueries

WITH cte AS (SELECT ...)Common Table Expression (named subquery)

WITH cte1 AS (...), cte2 AS (...)Multiple CTEs in one query

WITH RECURSIVE cte(cols) AS (base UNION ALL recursive)Recursive CTE for hierarchical / graph data

SELECT * FROM (SELECT ...) AS subInline subquery in FROM clause

WHERE col = (SELECT MAX(col) FROM t)Scalar subquery

WHERE col IN (SELECT id FROM other)Subquery in IN

WHERE EXISTS (SELECT 1 FROM t WHERE ...)Exists check (stops at first match)

WHERE NOT EXISTS (...)Rows with no matching related row

LEFT JOIN LATERAL (SELECT ...) AS sub ON trueLATERAL join - subquery references outer row

WITH ins AS (INSERT ... RETURNING *) SELECT * FROM insCTE with data-modifying statement

Indexes

CREATE INDEX idx ON t (col)Basic B-tree index

CREATE UNIQUE INDEX idx ON t (col)Unique B-tree index

CREATE INDEX idx ON t (col1, col2)Composite index (order matters)

CREATE INDEX idx ON t (col DESC NULLS LAST)Index with sort order matching query

CREATE INDEX CONCURRENTLY idx ON t (col)Build index without locking table writes

CREATE INDEX ON t USING GIN (col)GIN index for JSONB / array / full-text search

CREATE INDEX ON t USING GIST (col)GiST index for geometric, range, or full-text types

CREATE INDEX ON t USING HASH (col)Hash index - fast equality only

CREATE INDEX ON t USING BRIN (col)BRIN index - very large, naturally ordered tables

CREATE INDEX ON t (lower(col))Expression index for case-insensitive queries

CREATE INDEX ON t (col) WHERE active = truePartial index - only indexes matching rows

DROP INDEX CONCURRENTLY idxDrop index without locking

REINDEX TABLE tRebuild all indexes on a table

REINDEX TABLE CONCURRENTLY tRebuild all indexes without locking table (PG 12+)

EXPLAIN SELECT ...Show query execution plan (estimated costs)

EXPLAIN ANALYZE SELECT ...Show plan with actual execution stats

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...Detailed plan with buffer usage

Transactions

BEGINStart a transaction

COMMITCommit current transaction

ROLLBACKRoll back current transaction

SAVEPOINT sp1Create a savepoint within a transaction

ROLLBACK TO SAVEPOINT sp1Roll back to savepoint (transaction stays open)

RELEASE SAVEPOINT sp1Remove a savepoint

SET TRANSACTION ISOLATION LEVEL READ COMMITTEDDefault level - sees committed rows at each statement

SET TRANSACTION ISOLATION LEVEL REPEATABLE READSnapshot at transaction start, no phantom reads

SET TRANSACTION ISOLATION LEVEL SERIALIZABLEStrictest - transactions appear fully sequential

SELECT ... FOR UPDATELock rows to prevent concurrent updates

SELECT ... FOR SHAREShared lock - others can read but not update

SELECT ... SKIP LOCKEDSkip locked rows instead of waiting

SELECT ... NOWAITFail immediately if lock cannot be acquired

LOCK TABLE t IN EXCLUSIVE MODEExplicit table-level lock

Insert, Update, Delete & Upsert

INSERT INTO t (col) VALUES (val)Insert single row

INSERT INTO t (col) VALUES (v1), (v2)Insert multiple rows at once

INSERT INTO t SELECT * FROM srcInsert from query result

INSERT INTO t (...) VALUES (...) RETURNING idInsert and return generated values

UPDATE t SET col = val WHERE id = 1Update specific rows

UPDATE t SET col = val RETURNING *Update and return modified rows

UPDATE t SET col = src.col FROM src WHERE t.id = src.idUpdate from another table

DELETE FROM t WHERE id = 1Delete specific rows

DELETE FROM t RETURNING *Delete and return removed rows

INSERT INTO t (...) VALUES (...) ON CONFLICT (col) DO UPDATE SET col = EXCLUDED.colUpsert - update on duplicate key

ON CONFLICT (col) DO NOTHINGUpsert - ignore on duplicate key

ON CONFLICT ON CONSTRAINT constraint_name DO UPDATE ...Upsert targeting a named constraint

Backup & Restore

pg_dump dbname > dump.sqlDump database to plain SQL file

pg_dump -Fc dbname > dump.pgcDump in custom compressed format (preferred)

pg_dump -Fd dbname -f outdir/Dump in directory format (parallelisable)

pg_dump -t tablename dbname > table.sqlDump a single table

pg_dump -s dbname > schema.sqlSchema only (no data)

pg_dump --data-only dbname > data.sqlData only (no schema)

pg_dumpall > all.sqlDump all databases and roles

psql dbname < dump.sqlRestore from plain SQL file

pg_restore -d dbname dump.pgcRestore from custom format dump

pg_restore --clean -d dbname dump.pgcRestore, dropping objects first

pg_restore -j 4 -d dbname dump.pgcParallel restore with 4 workers

vacuumdb -d dbnameRun VACUUM on a database

VACUUM ANALYZE tReclaim space and update planner statistics

pg_basebackup -D /backup --format=tPhysical base backup in tar format