PostgreSQL cheat sheet

-- Auto-incrementing IDs.
id BIGSERIAL PRIMARY KEY                          -- legacy
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY -- modern

-- Native types worth knowing.
UUID                                  -- uuid_generate_v4() with extension
JSONB                                 -- binary, indexable, queryable JSON
TEXT                                  -- unbounded varchar; same perf as VARCHAR
TIMESTAMPTZ                           -- timezone-aware timestamp (the default for events)
INTERVAL                              -- '7 days', '1 month', '90 minutes'
ARRAY (e.g., INTEGER[], TEXT[])       -- first-class arrays

payload -> 'field'              -- get JSON value (returns jsonb)
payload ->> 'field'             -- get text value
payload #> '{a,b,c}'             -- nested by path -> jsonb
payload #>> '{a,b,c}'            -- nested by path -> text
payload @> '{"key":"val"}'       -- contains (great with GIN index)
payload <@ '{"key":"val"}'       -- contained by
payload ? 'field'                -- key exists
payload ?| array['a','b']        -- any of these keys
payload ?& array['a','b']        -- all of these keys
payload || '{"new":1}'           -- merge (shallow)
payload - 'field'                -- remove key
payload #- '{a,b}'               -- remove nested

-- Update inside JSON.
UPDATE t SET payload = jsonb_set(payload, '{user,plan}', '"pro"'::jsonb);

-- Index for fast containment queries.
CREATE INDEX events_payload_idx ON events USING GIN (payload jsonb_path_ops);

-- Declare and insert.
tags TEXT[]                                   -- column type
ARRAY['a','b','c']                             -- literal
'{a,b,c}'::TEXT[]                              -- string-literal cast

-- Operators.
ARRAY[1,2,3] || ARRAY[4,5]                     -- concat -> {1,2,3,4,5}
arr[1]                                         -- 1-based index
'a' = ANY(arr)                                 -- contains
arr @> ARRAY['a']                              -- left contains right
array_length(arr, 1)                           -- length

-- Unnest into rows.
SELECT id, tag FROM posts, UNNEST(tags) AS tag;

-- Get back the auto-generated row.
INSERT INTO users (email) VALUES ('a@x.com')
RETURNING id, created_at;

-- Skip duplicates (UPSERT — Postgres-style).
INSERT INTO users (id, email) VALUES (1, 'a@x.com')
ON CONFLICT (id) DO NOTHING;

INSERT INTO users (id, email) VALUES (1, 'a@x.com')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email,
                              updated_at = CURRENT_TIMESTAMP;

-- Conditional update.
INSERT INTO target (id, value, updated_at)
VALUES (1, 'new', CURRENT_TIMESTAMP)
ON CONFLICT (id) DO UPDATE SET value = EXCLUDED.value
WHERE target.updated_at < EXCLUDED.updated_at;

-- Most recent order per user — Postgres shortcut for the ROW_NUMBER pattern.
SELECT DISTINCT ON (user_id)
  user_id, order_date, total
FROM   orders
ORDER  BY user_id, order_date DESC;

-- Numbers 1..100.
SELECT generate_series(1, 100) AS n;

-- Date spine: every day in 2026.
SELECT generate_series(
  DATE '2026-01-01',
  DATE '2026-12-31',
  INTERVAL '1 day'
)::DATE AS day;

-- Combine to find missing dates in a series (calendar-table pattern).
SELECT d.day
FROM   generate_series(DATE '2026-01-01', DATE '2026-12-31', INTERVAL '1 day') AS d(day)
LEFT JOIN events e ON e.day = d.day
WHERE  e.day IS NULL;

s ~ 'pattern'             -- match (case-sensitive)
s ~* 'pattern'            -- match (case-insensitive)
s !~ 'pattern'            -- not match
s !~* 'pattern'           -- not match (case-insensitive)

REGEXP_REPLACE(s, '[0-9]+', '?', 'g')         -- replace all
REGEXP_MATCHES(s, '([a-z]+)@([a-z.]+)')       -- capture groups
REGEXP_SPLIT_TO_TABLE(s, ',')                  -- split into rows

-- For each user, get their 3 most recent orders.
SELECT u.id, u.email, o.id AS order_id, o.total
FROM   users u
JOIN LATERAL (
  SELECT id, total
  FROM   orders
  WHERE  orders.user_id = u.id
  ORDER  BY created_at DESC
  LIMIT  3
) o ON TRUE;

\l                  -- list databases
\dt                 -- list tables in current schema
\dt *.*             -- all tables across all schemas
\d table_name       -- describe table (columns, indexes, FKs)
\d+ table_name      -- describe with size and stats
\dn                 -- list schemas
\df                 -- list functions
\dv                 -- list views
\du                 -- list users / roles
\dx                 -- list installed extensions
\timing             -- toggle query timing
\x                  -- toggle expanded output
\copy ... FROM 'file.csv' CSV HEADER       -- bulk load
\?                  -- help on psql commands
\q                  -- quit

-- Table sizes.
SELECT
  schemaname || '.' || tablename AS full_name,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size
FROM   pg_tables
WHERE  schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER  BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- Active queries.
SELECT pid, query_start, state, query
FROM   pg_stat_activity
WHERE  state <> 'idle'
ORDER  BY query_start;

-- Slowest queries (with pg_stat_statements extension).
SELECT calls, mean_exec_time, query
FROM   pg_stat_statements
ORDER  BY mean_exec_time DESC
LIMIT  20;

-- Cancel a runaway query.
SELECT pg_cancel_backend(pid);
SELECT pg_terminate_backend(pid);  -- harder kill