Languages

PostgreSQL Cheat Sheet — psql Commands and SQL Features

The PostgreSQL-specific commands and features beyond standard SQL: psql shortcuts, JSONB, upserts, window functions, and handy admin queries.

Put it into practice with the matching tool.

Run PostgreSQL in the browser →

psql meta-commands

psql -h host -U user -d dbConnect to a database

\lList databases

\c dbnameConnect to another database

\dtList tables

\d tableDescribe a table — columns, indexes, constraints

\duList roles

\xToggle expanded output for wide rows

\timingShow how long each query takes

\i file.sqlRun commands from a file

\qQuit

Types and tables

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEYAuto-increment primary key (modern form of SERIAL)

uuid DEFAULT gen_random_uuid()Random UUID default

timestamptz DEFAULT now()Timestamp with time zone

text / varchar(n)Strings — text is preferred unless you need a limit

jsonbBinary JSON — indexable and queryable

text[]Array column

numeric(10,2)Exact decimal, for money

Writing data

INSERT ... RETURNING idReturn values from inserted rows

ON CONFLICT (email) DO NOTHINGSkip rows that violate a unique constraint

ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.nameUpsert

UPDATE t SET ... FROM other WHERE ...Update using a join

COPY t FROM '/path/file.csv' CSV HEADERBulk-load a CSV (use \copy in psql for a local file)

JSONB

data->'user'Get a JSON field as JSON

data->>'name'Get a JSON field as text

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

data @> '{"active": true}'Contains — can use a GIN index

data ? 'key'Key exists

jsonb_set(data, '{name}', '"Ana"')Return a copy with a value replaced

Queries

WITH recent AS (SELECT ...) SELECT ...Common table expression

row_number() OVER (PARTITION BY a ORDER BY b)Window function — number rows per group

SELECT DISTINCT ON (user_id) ...First row per group (with ORDER BY)

ILIKE '%term%'Case-insensitive pattern match

string_agg(name, ', ')Join values from many rows into one string

generate_series(1, 10)Produce a series of numbers or dates

Indexes and admin

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

CREATE INDEX ON t USING gin (data)GIN index for JSONB or arrays

EXPLAIN ANALYZE SELECT ...Run a query and show the real plan and timings

SELECT * FROM pg_stat_activitySee running queries and connections

SELECT pg_size_pretty(pg_database_size(current_database()))Database size

VACUUM ANALYZE tReclaim space and refresh planner statistics

Related guides

Practising SQL in the Browser: SQLite vs PostgreSQL

More cheat sheets

Regex →Markdown →SQL →JS Arrays →TypeScript →JS Strings →