PostgreSQL Cheat Sheet

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  customer_id bigint NOT NULL,
  total numeric(10,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE orders RENAME TO customer_orders;

CREATE TABLE orders_backup AS SELECT * FROM orders;

ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'new';

ALTER TABLE orders RENAME COLUMN total TO amount;

ALTER TABLE orders DROP COLUMN status;

ALTER TABLE orders
  ALTER COLUMN total TYPE numeric(12,2) USING total::numeric(12,2);

ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'new';
ALTER TABLE orders ALTER COLUMN status DROP DEFAULT;

TRUNCATE TABLE orders RESTART IDENTITY CASCADE;

DROP TABLE IF EXISTS orders_backup;

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'orders'
ORDER BY ordinal_position;

INSERT INTO orders (customer_id, total)
VALUES (1, 49.90), (2, 12.00);

INSERT INTO orders_archive (id, total)
SELECT id, total FROM orders WHERE created_at < now() - interval '1 year';

INSERT INTO orders (customer_id, total)
VALUES (1, 49.90)
RETURNING id;

UPDATE orders SET status = 'shipped' WHERE id = 42;

UPDATE orders o
SET total = t.amount
FROM order_totals t
WHERE t.order_id = o.id;

DELETE FROM orders WHERE created_at < now() - interval '2 years';

INSERT INTO orders (id, total)
VALUES (1, 49.90)
ON CONFLICT (id) DO UPDATE SET total = EXCLUDED.total;

INSERT INTO orders (id, total)
VALUES (1, 49.90)
ON CONFLICT DO NOTHING;

SELECT email, count(*)
FROM customers
GROUP BY email
HAVING count(*) > 1;

DELETE FROM customers a
USING customers b
WHERE a.id > b.id AND a.email = b.email;

SELECT * FROM orders ORDER BY created_at DESC LIMIT 50 OFFSET 100;

SELECT coalesce(nickname, first_name, 'there') AS greeting FROM customers;

CREATE OR REPLACE VIEW recent_orders AS
SELECT * FROM orders WHERE created_at > now() - interval '30 days';

CREATE MATERIALIZED VIEW daily_totals AS
SELECT date_trunc('day', created_at) AS day, sum(total) AS total
FROM orders GROUP BY 1;

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_totals;

SELECT to_char(created_at, 'YYYY-MM-DD HH24:MI') FROM orders;

SELECT delivered_at::date - created_at::date AS days,
       age(delivered_at, created_at)      AS interval
FROM orders;

SELECT date_trunc('month', created_at) AS month, count(*)
FROM orders GROUP BY 1 ORDER BY 1;

SELECT generate_series('2026-01-01'::date, '2026-01-31'::date, '1 day') AS day;

SELECT customer_id, string_agg(sku, ', ' ORDER BY sku) AS skus
FROM order_items GROUP BY customer_id;

SELECT payload->>'status' AS status
FROM events
WHERE payload->'user'->>'id' = '42';

SELECT created_at, sum(total) OVER (ORDER BY created_at) AS running_total
FROM orders;

CREATE INDEX orders_created_at_idx ON orders (created_at);

CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);

CREATE UNIQUE INDEX customers_email_idx ON customers (lower(email));

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';

DROP INDEX CONCURRENTLY IF EXISTS orders_status_idx;

ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY (id);

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;

ALTER TABLE orders ADD CONSTRAINT orders_total_positive CHECK (total >= 0);

SELECT tablename FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename;

SELECT datname FROM pg_database WHERE datistemplate = false;

CREATE DATABASE analytics WITH ENCODING 'UTF8' TEMPLATE template0;

SELECT pg_size_pretty(pg_total_relation_size('orders')) AS total,
       pg_size_pretty(pg_relation_size('orders'))       AS table_only;

SELECT pid, state, now() - query_start AS runtime, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY runtime DESC;

SELECT pg_cancel_backend(12345);    -- ask it to stop
SELECT pg_terminate_backend(12345); -- drop the connection

VACUUM (ANALYZE) orders;

GRANT USAGE ON SCHEMA public TO analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;

SELECT version(), current_database(), current_user;

psql "postgresql://user@host:5432/analytics"
\c analytics

\l

\dt
\dv
\di

\d orders
\d+ orders

\dn
\du
\df

\x auto

\timing on

\copy (SELECT * FROM orders) TO 'orders.csv' WITH CSV HEADER

\i migration.sql
\q