PostgreSQL Cheatsheet

CREATE TABLE film (
  film_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title   TEXT NOT NULL,
  rating  TEXT NOT NULL,
  length  INT  NOT NULL,          -- minutes
  price   NUMERIC(4, 2)           -- NULL = not priced yet
);

SELECT rating, COUNT(*) AS films
FROM film
WHERE length > 60
GROUP BY rating
HAVING COUNT(*) > 1
ORDER BY films DESC
LIMIT 1;

SELECT title AS name, length / 60.0 AS hours  -- AS renames a column in the output
FROM film f                                   -- f is a table alias
WHERE title ILIKE '%lake%'                    -- ILIKE ignores case; ~ matches a regex
  AND rating <> 'R'                           -- <> (or !=) means "not equal"
ORDER BY length DESC, title                   -- sort by length, then by title
LIMIT 5 OFFSET 10;                            -- rows 11 to 15

FETCH FIRST 5 ROWS ONLY                       -- the SQL-standard way to write LIMIT 5

SELECT DISTINCT rating FROM film;             -- each rating once
SELECT DISTINCT ON (rating) rating, title, length
FROM film ORDER BY rating, length DESC;       -- the longest film per rating (PostgreSQL only)

ORDER BY price NULLS LAST                     -- NULLs sort last in ASC by default, first in DESC

-- USING: shorthand when both columns have the same name
SELECT * FROM rental JOIN payment USING (rental_id);

-- Self-join: a table joined to itself, here each employee with their manager
SELECT e.name AS employee, m.name AS manager
FROM staff e
LEFT JOIN staff m ON m.id = e.manager_id;

-- Find rows with no match: customers who never rented
SELECT c.name
FROM customer c
LEFT JOIN rental r ON r.customer_id = c.customer_id
WHERE r.rental_id IS NULL;

SELECT rating, COUNT(*) AS films, SUM(price), MIN(length), MAX(length)
FROM film
WHERE length > 60
GROUP BY rating
HAVING COUNT(*) > 1;

-- Several groupings in one query
GROUP BY GROUPING SETS ((rating), (price), ())  -- by rating, by price, and a grand total
GROUP BY ROLLUP (rating, price)                 -- (rating, price), (rating), ()
GROUP BY CUBE (rating, price)                   -- every combination, including (price) alone

SELECT rating, COUNT(*) FILTER (WHERE price > 1) -- count only some rows in each group
FROM film GROUP BY rating;

-- One value: films longer than the average
SELECT title FROM film WHERE length > (SELECT AVG(length) FROM film);

-- A list: customers who rented something
SELECT name FROM customer WHERE customer_id IN (SELECT customer_id FROM rental);

-- EXISTS: stops at the first match, ignores NULLs
SELECT name FROM customer c
WHERE EXISTS (SELECT 1 FROM rental r WHERE r.customer_id = c.customer_id);

-- ANY / ALL: compare with every value a subquery returns
WHERE length > ALL (SELECT length FROM film WHERE rating = 'G')  -- longer than every G film
WHERE rating = ANY (ARRAY['G', 'PG'])                            -- same as IN

-- Correlated: the inner query uses the outer row (c), so it runs once per customer
SELECT name, (SELECT COUNT(*) FROM rental r WHERE r.customer_id = c.customer_id) AS rentals
FROM customer c;

WITH long_films AS (
  SELECT * FROM film WHERE length > 100
)
SELECT rating, COUNT(*) FROM long_films GROUP BY rating;

INSERT INTO film (title, rating, length, price)
VALUES ('Iron Gate', 'PG', 99, 2.99),          -- several rows in one statement
       ('Jade Moon', 'G', 81, 0.99)
RETURNING film_id;                             -- send back the new ids

UPDATE film SET price = price + 1 WHERE rating = 'R' RETURNING title, price;

UPDATE film f SET price = p.new_price          -- update from another table
FROM price_list p WHERE p.film_id = f.film_id;

DELETE FROM film WHERE price IS NULL;

DELETE FROM rental r USING customer c          -- delete using another table
WHERE r.customer_id = c.customer_id AND c.name = 'Ben';

BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;            -- or ROLLBACK; to undo both

SAVEPOINT before_risky;  ROLLBACK TO SAVEPOINT before_risky;   -- undo part of a transaction

COPY film FROM '/srv/data/film.csv' WITH (FORMAT csv, HEADER true);   -- file on the database server
\copy film FROM 'film.csv' WITH (FORMAT csv, HEADER true)            -- file on your machine (psql)
\copy (SELECT * FROM film WHERE rating = 'PG') TO 'pg.csv' WITH (FORMAT csv, HEADER true)

CREATE TABLE rental (
  rental_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customer ON DELETE CASCADE,
  film_id     BIGINT NOT NULL REFERENCES film,
  rented_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  days        INT CHECK (days BETWEEN 1 AND 30),
  UNIQUE (customer_id, film_id, rented_at)
);

ALTER TABLE rental RENAME TO loan;
ALTER TABLE loan ADD COLUMN note TEXT;
ALTER TABLE loan DROP COLUMN note;
ALTER TABLE loan RENAME COLUMN days TO loan_days;
ALTER TABLE loan ALTER COLUMN loan_days TYPE SMALLINT;
ALTER TABLE loan ALTER COLUMN loan_days SET DEFAULT 7;
ALTER TABLE loan ALTER COLUMN loan_days SET NOT NULL;
ALTER TABLE loan ADD CONSTRAINT loan_days_max CHECK (loan_days <= 60);

CREATE TABLE pg_films AS SELECT * FROM film WHERE rating = 'PG';  -- copy rows (no keys or constraints)
CREATE TABLE film_copy (LIKE film INCLUDING ALL);                 -- copy structure, no rows
CREATE TEMP TABLE scratch (id INT);                               -- dropped when your session ends

TRUNCATE film RESTART IDENTITY;   -- delete all rows fast, reset the id counter
DROP TABLE IF EXISTS film_copy;   -- add CASCADE to drop views and keys that depend on it

SELECT title,
  CASE
    WHEN length < 70  THEN 'short'
    WHEN length < 110 THEN 'medium'
    ELSE 'long'
  END AS size,
  COALESCE(price, 0)        AS price_or_zero,   -- first value that is not NULL
  NULLIF(price, 0)          AS price_or_null,   -- NULL if price = 0; avoids divide-by-zero
  CAST(length AS TEXT)      AS length_text,     -- or length::text
  '2026-10-19'::date + 7    AS due              -- dates plus days
FROM film;

CREATE INDEX ON rental (customer_id);                    -- B-tree, the default
CREATE INDEX CONCURRENTLY ON rental (film_id);           -- build without blocking writes
CREATE INDEX ON rental (customer_id, rented_at DESC);    -- multicolumn: leading column matters
CREATE INDEX ON customer (lower(email));                 -- expression: for WHERE lower(email) = …
CREATE INDEX ON rental (rented_at) WHERE returned = false;  -- partial: only some rows
CREATE INDEX ON film USING GIN (tags);                   -- arrays, JSONB, full-text search
DROP INDEX rental_film_id_idx;

EXPLAIN SELECT …;              -- the plan, with estimated costs
EXPLAIN ANALYZE SELECT …;      -- runs the query and shows real times

CREATE VIEW long_films AS SELECT * FROM film WHERE length > 100;   -- a saved query
CREATE MATERIALIZED VIEW rating_stats AS
  SELECT rating, COUNT(*) FROM film GROUP BY rating;                -- a saved result
REFRESH MATERIALIZED VIEW rating_stats;                             -- recompute it

CREATE FUNCTION films_rated(r TEXT) RETURNS BIGINT
LANGUAGE sql AS $$ SELECT COUNT(*) FROM film WHERE rating = r $$;
SELECT films_rated('PG');   -- 3

CREATE FUNCTION touch() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END $$;
CREATE TRIGGER film_touch BEFORE UPDATE ON film
FOR EACH ROW EXECUTE FUNCTION touch();

-- Delete duplicate rows, keeping the one with the lowest id
DELETE FROM customer a USING customer b
WHERE a.email = b.email AND a.customer_id > b.customer_id;

-- Rows in one table but not the other
SELECT * FROM film EXCEPT SELECT * FROM film_copy;

-- A random whole number from 1 to 10
SELECT floor(random() * 10 + 1)::int;   -- PostgreSQL 17+: random(1, 10)

-- A row per day, for reports with no gaps
SELECT d::date FROM generate_series('2026-10-01', '2026-10-07', interval '1 day') AS d;