Cheatsheet

-- ===== Schema =====
CREATE TABLE customers (
  id         bigserial PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  name       text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id           bigserial PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  total_cents  bigint NOT NULL CHECK (total_cents >= 0),
  status       text NOT NULL,
  payload      jsonb NOT NULL DEFAULT '{}',
  created_at   timestamptz NOT NULL DEFAULT now()
);

-- Common-sense indexes
CREATE INDEX ix_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC);

-- ===== Queries =====
SELECT * FROM orders WHERE customer_id = 42 AND status IN ('paid','shipped');
SELECT count(*), status FROM orders GROUP BY status;
SELECT customer_id, sum(total_cents) AS total
FROM orders WHERE status = 'paid'
GROUP BY customer_id ORDER BY total DESC LIMIT 10;

-- Distinct on (oldest row per group)
SELECT DISTINCT ON (customer_id) *
FROM orders ORDER BY customer_id, created_at;

-- Upsert
INSERT INTO customers (email, name) VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- ===== Window functions =====
SELECT id, customer_id, total_cents,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_cents DESC) AS rk,
       SUM(total_cents) OVER (PARTITION BY customer_id) AS lifetime
FROM orders;

-- 7-day moving average
SELECT day, revenue,
       avg(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7
FROM daily_revenue;

-- ===== JSON =====
-- Index
CREATE INDEX ix_orders_payload_gin ON orders USING gin (payload jsonb_path_ops);

-- Read
SELECT id, payload->>'source' AS src FROM orders;

-- Filter
SELECT id FROM orders WHERE payload @> '{"source":"web"}';

-- Write
UPDATE orders SET payload = payload || '{"shipped":true}' WHERE id = 1;
UPDATE orders SET payload = jsonb_set(payload, '{amount}', '4995') WHERE id = 1;

-- ===== CTE =====
WITH recent AS (
  SELECT * FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT customer_id, count(*) FROM recent GROUP BY customer_id;

-- Recursive CTE
WITH RECURSIVE tree AS (
  SELECT id, parent_id, name FROM nodes WHERE parent_id IS NULL
  UNION ALL
  SELECT n.id, n.parent_id, n.name FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;

-- ===== Transactions =====
BEGIN;
  UPDATE customers SET orders_count = orders_count + 1 WHERE id = 42;
  INSERT INTO orders(customer_id, total_cents, status) VALUES (42, 4995, 'new');
COMMIT;

-- Save points
BEGIN;
  SAVEPOINT before_thing;
  ...
  ROLLBACK TO SAVEPOINT before_thing;
COMMIT;

-- Isolation
BEGIN ISOLATION LEVEL SERIALIZABLE;
  -- ...
COMMIT;

-- ===== Explain =====
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

-- Look for: Index Scan / Index Only Scan; Rows ~= Loops; small Buffers reads

-- ===== Useful built-ins =====
SELECT now(), current_date, age('2026-06-18'::date);
SELECT date_trunc('day', created_at) AS day, count(*) FROM orders GROUP BY 1;
SELECT generate_series(1, 10);
SELECT array_agg(email) FROM customers;
SELECT string_agg(email, ', ') FROM customers;
SELECT regexp_matches('hello world', '(\\w+)\\s(\\w+)');

-- ===== Roles + permissions =====
CREATE ROLE app_user LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE shop TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

-- Read-only role
CREATE ROLE app_ro LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE shop TO app_ro;
GRANT USAGE ON SCHEMA public TO app_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;

-- ===== Backups =====
pg_dump -h prod -U postgres shop > backup-$(date +%F).sql
pg_dump -Fc -h prod -U postgres shop > backup-$(date +%F).dump   # custom format
psql -h staging -U postgres shop < backup-2026-06-18.sql
pg_restore -h staging -U postgres -d shop backup-2026-06-18.dump

-- COPY for bulk load
COPY orders (customer_id, total_cents, status) FROM '/tmp/orders.csv' WITH (FORMAT csv, HEADER true);

-- ===== Operations =====
VACUUM ANALYZE;
REINDEX TABLE orders;
CLUSTER orders USING ix_orders_customer_status_created;

-- ===== psql tips =====
\dt                            list tables
\d orders                      describe table
\di                            list indexes
\du                            list roles
\timing on                      show query time
\watch 2                        re-run every 2 seconds
\copy orders FROM 'x.csv' csv header

-- ===== Pitfalls =====
-- - WHERE DATE(created_at) = today   -> index unusable
-- - SELECT * in transactions -> long-running tx
-- - REFRESH MATERIALIZED VIEW (without CONCURRENTLY) -> locks reads
-- - No VACUUM ANALYZE after bulk load -> bad plans
-- - Forgetting indexes on FK columns -> JOIN performance

-- psql \d \dt \df \du \timing
-- SELECT INSERT UPDATE DELETE JOIN GROUP HAVING WITH WINDOW