PostgreSQL Cheat Sheet

CREATE TABLE users (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  data JSONB NOT NULL DEFAULT '{}'
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  tags TEXT[] DEFAULT '{}'
);

CREATE TYPE status AS ENUM ('draft', 'published', 'archived');
ALTER TABLE posts ADD COLUMN status status DEFAULT 'draft';

SELECT '192.168.1.0/24'::cidr >> '192.168.1.5'::inet;
-- true (contains)

SELECT NOW() - INTERVAL '30 days' AS last_month;
SELECT age('2026-01-01', '1990-05-15');

SELECT data->'name' AS json_val,
       data->>'name' AS text_val
FROM events;

SELECT data#>>'{address,city}' FROM events;
-- Deep path extraction as text

SELECT * FROM events
WHERE data @> '{"type": "click"}'::jsonb;

SELECT * FROM events WHERE data ? 'email';
-- Has key 'email'

UPDATE events
SET data = jsonb_set(data, '{status}', '"active"')
WHERE id = 1;

SELECT jsonb_agg(name) FROM users;
SELECT jsonb_object_agg(key, value) FROM pairs;

SELECT * FROM posts WHERE 'sql' = ANY(tags);
SELECT * FROM posts WHERE 5 > ALL(scores);

SELECT author, array_agg(title)
FROM posts GROUP BY author;

SELECT unnest(tags) AS tag FROM posts;
-- Expand array to rows

SELECT * FROM posts
WHERE tags && ARRAY['sql','python'];
-- Arrays share elements

SELECT * FROM posts
WHERE tags @> ARRAY['sql','python'];
-- Contains all listed

UPDATE posts SET tags = array_append(tags, 'new');
UPDATE posts SET tags = array_remove(tags, 'old');

SELECT * FROM posts
WHERE to_tsvector('english', body)
  @@ to_tsquery('english', 'postgres & replication');

SELECT title, ts_rank(tsv, q) AS rank
FROM posts, to_tsquery('postgres') q
WHERE tsv @@ q ORDER BY rank DESC;

SELECT ts_headline('english', body,
  to_tsquery('postgres'),
  'StartSel=<b>, StopSel=</b>'
) FROM posts;

ALTER TABLE posts ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))
  ) STORED;
CREATE INDEX idx_tsv ON posts USING GIN(tsv);

SELECT * FROM posts
WHERE tsv @@ phraseto_tsquery('database replication');

WITH active_users AS (
  SELECT * FROM users WHERE active = true
)
SELECT * FROM active_users WHERE created_at > NOW() - INTERVAL '7 days';

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

WITH active AS MATERIALIZED (
  SELECT * FROM users WHERE active
)
SELECT * FROM active;  -- Forces subquery evaluation

WITH
  recent AS (SELECT * FROM orders WHERE date > NOW() - '30d'::interval),
  totals AS (SELECT user_id, SUM(amount) FROM recent GROUP BY user_id)
SELECT u.name, t.sum FROM users u JOIN totals t ON u.id = t.user_id;

CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX idx_uniq ON users(email);

CREATE INDEX idx_data ON events USING GIN(data);
CREATE INDEX idx_tags ON posts USING GIN(tags);

CREATE INDEX idx_active ON users(email)
WHERE active = true;
-- Only indexes matching rows

CREATE INDEX idx_lower ON users(LOWER(email));
-- Index on computed value

CREATE INDEX idx_cover ON orders(user_id)
INCLUDE (total, created_at);
-- Index-only scans

CREATE INDEX idx_ts ON logs USING BRIN(created_at);
-- Compact index for sorted data

CREATE TABLE logs (
  id BIGSERIAL, ts TIMESTAMPTZ, msg TEXT
) PARTITION BY RANGE (ts);

CREATE TABLE logs_2026_01 PARTITION OF logs
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE orders (
  id SERIAL, region TEXT, amount NUMERIC
) PARTITION BY LIST (region);

CREATE TABLE orders_us PARTITION OF orders
  FOR VALUES IN ('us-east', 'us-west');

CREATE TABLE sessions (
  id UUID, data JSONB
) PARTITION BY HASH (id);

CREATE TABLE sessions_0 PARTITION OF sessions
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);

ALTER TABLE logs ATTACH PARTITION logs_old
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
ALTER TABLE logs DETACH PARTITION logs_old;

CREATE EXTENSION pg_trgm;
SELECT * FROM products
WHERE name % 'postgre'
ORDER BY similarity(name, 'postgre') DESC;

CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v4();
-- Or use built-in: gen_random_uuid()

CREATE EXTENSION pgcrypto;
SELECT crypt('password', gen_salt('bf'));
SELECT (crypt('password', hash) = hash) AS valid;

CREATE EXTENSION citext;
ALTER TABLE users ALTER COLUMN email TYPE citext;
-- Case-insensitive text

CREATE EXTENSION vector;
CREATE TABLE items (id SERIAL, embedding vector(1536));
SELECT * FROM items ORDER BY embedding <=> '[0.1,0.2,...]' LIMIT 5;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'a@b.com';

SELECT relname, n_live_tup, n_dead_tup,
  last_vacuum, last_autovacuum
FROM pg_stat_user_tables;

SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0;  -- Unused indexes

VACUUM ANALYZE users;      -- Update stats
VACUUM FULL users;         -- Reclaim space (locks table)
SELECT pg_size_pretty(pg_total_relation_size('users'));

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

\l          -- List databases
\dt         -- List tables
\d+ table   -- Describe table
\di         -- List indexes
\df         -- List functions
\x          -- Toggle expanded output

pg_dump -Fc mydb > backup.dump
pg_restore -d mydb backup.dump
pg_dump --schema-only mydb > schema.sql

CREATE ROLE readonly LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

SELECT pid, query, state FROM pg_stat_activity
WHERE state = 'active';
SELECT pg_cancel_backend(pid);   -- Graceful
SELECT pg_terminate_backend(pid); -- Force

SELECT generate_series('2026-01-01'::date, '2026-12-31', '1 month');
SELECT generate_series(1, 100) AS n;

SELECT u.name, r.title FROM users u,
LATERAL (
  SELECT title FROM posts WHERE author_id = u.id
  ORDER BY created_at DESC LIMIT 3
) r;

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'active') AS active,
  AVG(score) FILTER (WHERE score > 0) AS avg_positive
FROM users;

SELECT region, product, SUM(sales)
FROM orders
GROUP BY GROUPING SETS (
  (region, product), (region), (product), ()
);

COPY users(name, email) FROM '/tmp/users.csv' CSV HEADER;
COPY (SELECT * FROM users) TO '/tmp/export.csv' CSV HEADER;

INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = NOW();