PostgreSQL Cheat Sheet

psql -h hostname -U username db_name
\q
\l
\c db_name
\dt
\d table_name
\?

\copy (SELECT * FROM table) TO '/tmp/out.csv' CSV HEADER
\i /path/to/file.sql

CREATE DATABASE mydb;
DROP DATABASE mydb;

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  total NUMERIC(10,2) NOT NULL
);

INTEGER, BIGINT, SERIAL, BIGSERIAL
TEXT, VARCHAR(n), CHAR(n)
BOOLEAN
NUMERIC, REAL, DOUBLE PRECISION
DATE, TIME, TIMESTAMP, TIMESTAMPTZ
INTERVAL
UUID
JSON, JSONB

INSERT INTO users (email) VALUES ('a@test.com');

INSERT INTO orders (user_id, total)
VALUES (1, 99.99);

SELECT column_list
FROM table
WHERE condition
ORDER BY column
LIMIT n OFFSET m;

SELECT email FROM users WHERE email LIKE '%@test.com';

= <> != < > <= >=
IN, BETWEEN, LIKE, ILIKE
IS NULL, IS NOT NULL

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
CROSS JOIN

SELECT u.email, o.total
FROM users u
JOIN orders o ON o.user_id = u.id;

COUNT(), SUM(), AVG(), MIN(), MAX()

SELECT user_id, SUM(total)
FROM orders
GROUP BY user_id;

SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 1;

SELECT *
FROM users
WHERE id IN (SELECT user_id FROM orders);

SELECT *
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

UNION
UNION ALL

SELECT email FROM users
UNION
SELECT email FROM newsletter;

SELECT data->'key' FROM table;
SELECT data->>'key' FROM table;

SELECT *
FROM products
WHERE attributes @> '{"color":"red"}';

CREATE INDEX idx_products_attr
ON products USING GIN (attributes);

CREATE INDEX idx_name ON table(column);
CREATE UNIQUE INDEX idx_unique ON table(column);
DROP INDEX idx_name;

CREATE INDEX idx_lower_email
ON users (LOWER(email));

BEGIN;
COMMIT;
ROLLBACK;
SAVEPOINT sp1;
ROLLBACK TO sp1;

WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at > now() - INTERVAL '7 days'
)
SELECT * FROM recent_orders;

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

ROW_NUMBER()
RANK()
DENSE_RANK()
LAG()
LEAD()
SUM() OVER (...)

SELECT user_id,
       total,
       SUM(total) OVER (PARTITION BY user_id) AS user_total
FROM orders;

LENGTH(), LOWER(), UPPER(), INITCAP()
SUBSTRING(), REPLACE(), TRIM()
CONCAT() or ||

ABS(), ROUND(), CEIL(), FLOOR(), MOD(), SQRT()

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP

DATE_TRUNC('month', created_at)
EXTRACT(YEAR FROM created_at)

'2024-01-01'::date + INTERVAL '7 days'

COALESCE(a, b)
NULLIF(a, b)

COPY table FROM '/path/file.csv' CSV HEADER;
COPY (SELECT * FROM table) TO '/path/out.csv' CSV HEADER;

EXPLAIN SELECT ...
EXPLAIN ANALYZE SELECT ...

SHOW work_mem;
SHOW shared_buffers;

SELECT * FROM pg_locks;

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS uuid-ossp;

pg_stat_activity
pg_stat_user_tables
pg_indexes

SELECT * FROM pg_stat_activity;