> ## Documentation Index > Fetch the complete documentation index at: https://miliracle.online/llms.txt > Use this file to discover all available pages before exploring further. # PostgreSQL Cheatsheet > The PostgreSQL you use every day on one page: query order, filters, joins, grouping, set operations, CTEs, upserts, tables, types, indexes and psql, each with a worked example. > This is the Markdown version of an interactive page. Every animated figure on the web page is replaced here by a table or code block with the same facts. Source: adapted from the [Neon PostgreSQL Tutorial](https://neon.com/postgresql/tutorial). Additions and modern defaults are noted in the text. You write `SELECT` first. PostgreSQL runs it fifth. That one fact explains half of the confusing errors in SQL: why `WHERE` can't see a column name you just made up, why `HAVING` exists at all, and why `LIMIT` without `ORDER BY` gives you random rows. This page is a cheatsheet with a twist. Each part has the SQL you can copy, and the tricky parts have a figure you can play with. Everything uses the same tiny tables, so you can check every answer by hand. It follows the order of the [Neon PostgreSQL Tutorial](https://neon.com/postgresql/tutorial), squeezed onto one page. > [!TIP] > > **In short** > > * A query runs in this order: `FROM` → `WHERE` → `GROUP BY` → `HAVING` → `SELECT` → `ORDER BY` → `LIMIT`. A clause can only use what earlier steps made. > * `WHERE` filters rows. `HAVING` filters groups. Test for missing values with `IS NULL`, never `= NULL`. > * `INNER JOIN` keeps matches only. `LEFT JOIN` keeps every row on the left and fills the gaps with NULL. > * `ON CONFLICT … DO UPDATE` is an upsert: insert, or update the row that is already there. > * An index lets PostgreSQL skip most of a table. `EXPLAIN` shows whether it did. ## The sample data Every example uses this `film` table. Hollow Pine has no price yet, so its `price` is NULL. ```sql theme={null} 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 ); ``` | film\_id | title | rating | length | price | | - | - | - | - | - | | 1 | Alien Dawn | PG | 95 | 2.99 | | 2 | Blue Harbor | G | 64 | 0.99 | | 3 | City of Glass | R | 132 | 4.99 | | 4 | Dust Road | PG | 110 | 2.99 | | 5 | Echo Lake | G | 88 | 0.99 | | 6 | Final Draft | R | 57 | 2.99 | | 7 | Grey Signal | PG-13 | 101 | 4.99 | | 8 | Hollow Pine | PG | 74 | NULL | ## The order a query runs Take the query in the figure at the top: "which rating has the most films longer than an hour?" ```sql theme={null} SELECT rating, COUNT(*) AS films FROM film WHERE length > 60 GROUP BY rating HAVING COUNT(*) > 1 ORDER BY films DESC LIMIT 1; ``` PostgreSQL does not read it top to bottom. It starts with the table, cuts rows, makes groups, cuts groups, and only then builds the columns you asked for. So the name `films` exists from step 5 on. `ORDER BY films` works. `WHERE films > 1` fails with `column "films" does not exist`. The figure steps through the query one clause at a time: | Step | Clause | What it does here | Result | | - | - | - | - | | 1 | `FROM film` | Start with every row | 8 films | | 2 | `WHERE length > 60` | Drop Final Draft (57 min) | 7 films | | 3 | `GROUP BY rating` | Make one group per rating | G: Blue Harbor, Echo Lake · PG: Alien Dawn, Dust Road, Hollow Pine · PG-13: Grey Signal · R: City of Glass | | 4 | `HAVING COUNT(*) > 1` | Drop groups with one film | G, PG left | | 5 | `SELECT rating, COUNT(*) AS films` | Build output columns; `films` is named here | (G, 2), (PG, 3), in no promised order | | 6 | `ORDER BY films DESC` | Sort, biggest first | (PG, 3), (G, 2) | | 7 | `LIMIT 1` | Keep the first row | (PG, 3) | The full logical order, including the parts not in the example, is `FROM` and `JOIN` → `WHERE` → `GROUP BY` → `HAVING` → `SELECT` (with window functions) → `DISTINCT` → `ORDER BY` → `LIMIT` / `OFFSET`. This is the order of *meaning*. The planner may do the work in a different order if the answer is the same. ## Filter rows: WHERE `WHERE` tests each row and keeps the ones where the test is true. Try each kind of test on the eight films. Each filter is `SELECT * FROM film` plus: | Filter | Rows that pass | Note | | - | - | - | | `WHERE rating = 'PG'` | Alien Dawn, Dust Road, Hollow Pine | Exact match | | `WHERE rating IN ('G', 'R')` | Blue Harbor, City of Glass, Echo Lake, Final Draft | Short for `rating = 'G' OR rating = 'R'` | | `WHERE length BETWEEN 90 AND 120` | Alien Dawn (95), Dust Road (110), Grey Signal (101) | Both ends included | | `WHERE title LIKE '_l%'` | Alien Dawn, Blue Harbor | `_` = one character, `%` = any number | | `WHERE price IS NULL` | Hollow Pine | The only way to find NULL | | `WHERE price = NULL` | none | Comparing with NULL is never true | | `WHERE rating = 'G' OR rating = 'R' AND length > 100` | Blue Harbor, City of Glass, Echo Lake | `AND` runs before `OR`: G OR (R AND long) | | `WHERE (rating = 'G' OR rating = 'R') AND length > 100` | City of Glass | Parentheses make `OR` run first | More ways to shape the rows you get back: ```sql theme={null} 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 ``` Without `ORDER BY`, PostgreSQL may return rows in any order, and the order can change between runs. `LIMIT` without `ORDER BY` gives you "some" rows, not "the first" rows. ## Join tables A join puts rows from two tables side by side. Here are a `customer` table and a `rental` table. Ben has never rented anything. Rental 104 points at customer 4, who doesn't exist. (A foreign key would stop that; this table has none, to show what each join does with an orphan.) `customer`: (1, Ana), (2, Ben), (3, Chi). `rental` (rental\_id, customer\_id, title): (101, 1, Alien Dawn), (102, 1, Echo Lake), (103, 3, Dust Road), (104, 4, Grey Signal). Query shape: `SELECT c.name, r.rental_id, r.title FROM customer c [join type] rental r ON r.customer_id = c.customer_id;` | Join | Rows | Result | | - | - | - | | `INNER JOIN` | 3 | (Ana, 101), (Ana, 102), (Chi, 103) | | `LEFT JOIN` | 4 | the 3 matches + (Ben, NULL, NULL) | | `RIGHT JOIN` | 4 | the 3 matches + (NULL, 104, Grey Signal) | | `FULL OUTER JOIN` | 5 | the 3 matches + Ben with NULLs + rental 104 with NULLs | | `CROSS JOIN` (no `ON`) | 12 | every customer with every rental: 3 × 4 | Other join forms: ```sql theme={null} -- 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; ``` `JOIN` on its own means `INNER JOIN`, and `LEFT JOIN` means `LEFT OUTER JOIN`. Avoid `NATURAL JOIN`: it joins on *every* column with the same name, so adding a column like `updated_at` to both tables quietly changes the result. ## Group and summarize `GROUP BY` folds many rows into one row per group. Aggregate functions (`COUNT`, `SUM`, `AVG`, `MIN`, `MAX`) then work inside each group. `SELECT , COUNT(*), ROUND(AVG(length), 1) FROM film GROUP BY ;` | Grouping | Group | Count | Avg length | Films | | - | - | - | - | - | | `GROUP BY rating` | G | 2 | 76.0 | Blue Harbor, Echo Lake | | | PG | 3 | 93.0 | Alien Dawn, Dust Road, Hollow Pine | | | PG-13 | 1 | 101.0 | Grey Signal | | | R | 2 | 94.5 | City of Glass, Final Draft | | `GROUP BY price` | 0.99 | 2 | 76.0 | Blue Harbor, Echo Lake | | | 2.99 | 3 | 87.3 | Alien Dawn, Dust Road, Final Draft | | | 4.99 | 2 | 116.5 | City of Glass, Grey Signal | | | NULL | 1 | 74.0 | Hollow Pine (all NULLs form one group) | | `GROUP BY ROLLUP (rating)` | the 4 rating groups above, plus: | | | | | | NULL (grand total) | 8 | 90.1 | all films | The rules worth remembering: * **Every column in `SELECT` must be grouped or aggregated.** `SELECT rating, title … GROUP BY rating` fails: which title would it show for PG? * **`WHERE` before, `HAVING` after.** `WHERE length > 60` drops rows before grouping. `HAVING COUNT(*) > 1` drops groups after. * **`COUNT(*)` counts rows; `COUNT(price)` counts non-NULL values.** On our table they give 8 and 7. ```sql theme={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; ``` ## Combine results: UNION, INTERSECT, EXCEPT A join puts columns side by side. A set operation stacks rows from two queries on top of each other. Both queries must return the same number of columns, with matching types. `SELECT name FROM jan_rentals` returns Ana, Ben, Chi, Ben. `SELECT name FROM feb_rentals` returns Chi, Dan, Ben. | Operation | Result | Rule | | - | - | - | | `UNION` | Ana, Ben, Chi, Dan | Either side, duplicates removed | | `UNION ALL` | Ana, Ben, Chi, Ben, Chi, Dan, Ben | Either side, duplicates kept | | `INTERSECT` | Ben, Chi | On both sides | | `EXCEPT` | Ana | In January but not in February | Use `UNION ALL` unless you need duplicates removed. Removing them means sorting or hashing every row, which costs time. `INTERSECT ALL` and `EXCEPT ALL` also exist and keep duplicates. ## Subqueries and CTEs A subquery is a query inside another query. ```sql theme={null} -- 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; ``` One trap: `NOT IN (subquery)` returns **no rows at all** if the subquery returns even one NULL. Use `NOT EXISTS` instead. A **CTE** (common table expression) names a subquery with `WITH`, so you can read the query top to bottom: ```sql theme={null} WITH long_films AS ( SELECT * FROM film WHERE length > 100 ) SELECT rating, COUNT(*) FROM long_films GROUP BY rating; ``` `WITH RECURSIVE` lets a CTE refer to itself. It is how SQL walks a tree, such as an org chart. Here `staff` holds Mia (the boss), Raj and Sol (report to Mia), Tom and Uma (report to Raj) and Val (reports to Tom). The query finds everyone under Raj. `staff` (id, name, manager\_id): (1, Mia, NULL), (2, Raj, 1), (3, Sol, 1), (4, Tom, 2), (5, Uma, 2), (6, Val, 4). ```sql theme={null} WITH RECURSIVE team AS ( SELECT id, name, 1 AS depth FROM staff WHERE id = 2 -- anchor: runs once UNION ALL SELECT s.id, s.name, t.depth + 1 FROM staff s JOIN team t ON s.manager_id = t.id -- recursive part: runs until it finds nothing new ) SELECT * FROM team; ``` | Pass | Part that runs | New rows | | - | - | - | | Anchor | First `SELECT` | (2, Raj, 1) | | 1 | Recursive part, joined to Raj | (4, Tom, 2), (5, Uma, 2) | | 2 | Recursive part, joined to Tom and Uma | (6, Val, 3) | | 3 | Recursive part, joined to Val | none, so it stops | Result: Raj, Tom, Uma, Val. Mia and Sol are never reached. Each pass only sees the rows found in the pass before. When a pass finds nothing new, the query stops. If your data has a loop (A manages B, B manages A), it never stops: add a depth limit such as `WHERE t.depth < 10`. ## Change data ```sql theme={null} 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'; ``` An `UPDATE` or `DELETE` without `WHERE` changes **every** row. Run the same `WHERE` as a `SELECT` first. ### Insert or update: upsert What should happen when you insert a row whose key already exists? Plain `INSERT` fails. `ON CONFLICT` lets you choose. `stock (sku PRIMARY KEY, qty)` holds (A1, 5) and (B2, 0). | Statement | psql prints | Table after | | - | - | - | | `INSERT INTO stock (sku, qty) VALUES ('C3', 4);` | `INSERT 0 1` | C3 added | | `INSERT INTO stock (sku, qty) VALUES ('A1', 3);` | `ERROR: duplicate key value violates unique constraint "stock_pkey"` | unchanged | | `… VALUES ('A1', 3) ON CONFLICT (sku) DO NOTHING;` | `INSERT 0 0` | unchanged, no error | | `… VALUES ('A1', 3) ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + EXCLUDED.qty;` | `INSERT 0 1` | A1 qty 5 → 8 | `EXCLUDED` is the row you tried to insert. `ON CONFLICT` needs a unique constraint or primary key on the columns you name. PostgreSQL 15 and later also has `MERGE`, the SQL-standard way to insert, update or delete in one statement. ### Transactions Group statements so they all happen or none do: ```sql theme={null} 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 ``` Why this matters is the whole subject of [ACID Transactions](/writing/database/acid-transactions.md). ### Import and export CSV ```sql theme={null} 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) ``` `COPY` reads files on the *server* and needs special rights. On a hosted database you almost always want psql's `\copy`, which reads the file on your machine. ## Make and change tables ```sql theme={null} 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 ``` Constraints at a glance: `PRIMARY KEY` (unique and not NULL), `REFERENCES` (a foreign key), `UNIQUE`, `NOT NULL`, `CHECK (…)` and `DEFAULT …`. [Keys and Relationships](/writing/database/keys-and-relationships.md) explains them with examples, including `ON DELETE CASCADE`. For auto-numbered ids, use `GENERATED ALWAYS AS IDENTITY` (PostgreSQL 10 and later). The older `SERIAL` still works and you will see it in tutorials, but it is a shortcut for a separate sequence, which is clumsier to manage. Sequences on their own: `CREATE SEQUENCE s;`, `nextval('s')`, `currval('s')`, `setval('s', 100)`. ### Common data types | Type | Use it for | | - | - | | `BIGINT`, `INT`, `SMALLINT` | Whole numbers (8, 4 and 2 bytes) | | `NUMERIC(p, s)` | Money and anything that must be exact | | `DOUBLE PRECISION`, `REAL` | Measurements where tiny rounding is fine | | `TEXT`, `VARCHAR(n)` | Strings; same speed, `VARCHAR(n)` adds a length limit | | `BOOLEAN` | `true`, `false`, or NULL | | `DATE`, `TIME` | A calendar day; a time of day | | `TIMESTAMPTZ` | A moment in time; prefer it over `TIMESTAMP` | | `INTERVAL` | A length of time: `interval '2 days'` | | `UUID` | Ids made anywhere: `gen_random_uuid()` | | `JSONB` | JSON you will query; prefer it over `JSON` | | `INT[]`, `TEXT[]` | Arrays: `ARRAY[1, 2]`, `tags @> '{sql}'` | | `BYTEA` | Raw bytes | You can also make your own: `CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');` for a fixed list, or `CREATE DOMAIN email AS TEXT CHECK (VALUE LIKE '%@%');` for a type with a rule. Avoid `CHAR(n)`: it pads with spaces and has no speed benefit. ## Conditional expressions ```sql theme={null} 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; ``` ## Make it fast: indexes and EXPLAIN Without an index, PostgreSQL finds rows by reading the whole table. An index is a sorted copy of one or more columns with pointers back to the rows. It is usually a B-tree, a few levels deep. `EXPLAIN` shows the plan PostgreSQL picked. Finding customer 42's rentals in a large `rental` table stored on 64 pages (page counts and costs are illustrative): | Run | Plan PostgreSQL picks | Pages read | | - | - | - | | `WHERE customer_id = 42`, no index | `Seq Scan on rental … Filter: (customer_id = 42)` | 64 of 64 (matches on 2) | | Same query after `CREATE INDEX ON rental (customer_id);` | `Index Scan using rental_customer_id_idx on rental … Index Cond: (customer_id = 42)` | 5: 3 index pages (root, branch, leaf) + 2 table pages | | `WHERE status = 'returned'` (matches \~90% of rows), with an index on `status` | `Seq Scan on rental … Filter: (status = 'returned'::text)` | 64 of 64; the index is skipped because almost every page has a match | ```sql theme={null} 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 ``` `EXPLAIN ANALYZE` really runs the statement. On an `UPDATE` or `DELETE`, wrap it in `BEGIN; … ROLLBACK;`. You will also see `Bitmap Index Scan` in plans: a middle path that reads the index first, then fetches the table pages in order. Every index speeds up some reads and slows down every write to that table. Add them for the queries you actually run. ### Views, functions and triggers ```sql theme={null} 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(); ``` ## psql commands psql is PostgreSQL's command-line client. Commands that start with a backslash belong to psql, not SQL, and need no semicolon. | Command | Does | | - | - | | `psql -h host -U user -d db` | Connect from your shell | | `\l` · `\c db` | List databases · switch to one | | `\dt` · `\d film` | List tables · describe one | | `\dn` · `\df` · `\dv` · `\du` | Schemas · functions · views · roles | | `\x` | Toggle wide rows to one field per line | | `\timing` | Show how long each query takes | | `\i file.sql` · `\e` | Run a file · edit the last query | | `\?` · `\h SELECT` | psql help · SQL help | | `\q` | Quit | ## Recipes ```sql theme={null} -- 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; ``` ## Check yourself Questions this document answers: * In what order does PostgreSQL run a query's clauses? `FROM`/`JOIN` → `WHERE` → `GROUP BY` → `HAVING` → `SELECT` → `DISTINCT` → `ORDER BY` → `LIMIT`/`OFFSET`. * Why does `WHERE films > 1` fail when `films` is a `SELECT` alias? `WHERE` runs before `SELECT`, so the alias doesn't exist yet. Use `HAVING COUNT(*) > 1`. `ORDER BY films` works because it runs after `SELECT`. * Why does `WHERE price = NULL` return nothing? Any comparison with NULL is unknown, never true. Use `IS NULL`. * What is the difference between `WHERE` and `HAVING`? `WHERE` filters rows before grouping; `HAVING` filters groups after, and can use aggregates. * How do `AND` and `OR` combine? `AND` binds tighter. Use parentheses. * `INNER` vs `LEFT` vs `RIGHT` vs `FULL` vs `CROSS JOIN`? Matches only; all left rows; all right rows; all rows from both; every pair. * How do you find rows with no match? `LEFT JOIN … WHERE right.id IS NULL`, or `NOT EXISTS`. * `UNION` vs `UNION ALL`? `UNION` removes duplicates; `UNION ALL` keeps them and is faster. * Why avoid `NOT IN (subquery)`? If the subquery returns a NULL, the result is empty. Use `NOT EXISTS`. * How does a recursive CTE work? The anchor runs once; the recursive part runs on the rows from the previous pass until no new rows appear. * How do you upsert? `INSERT … ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col` (or `DO NOTHING`). `MERGE` exists from PostgreSQL 15. * `COPY` vs `\copy`? `COPY` reads files on the server and needs privileges; `\copy` reads files on the client. * `SERIAL` or identity? Prefer `GENERATED ALWAYS AS IDENTITY`. * Why might PostgreSQL ignore an index? When the query matches most rows, a sequential scan is cheaper. * Does `EXPLAIN ANALYZE` run the query? Yes. Wrap writes in a transaction and roll back. ## Glossary | Term | Meaning | | - | - | | Clause | One part of a statement: `FROM`, `WHERE`, `GROUP BY` … | | Alias | A temporary name: `AS films` for a column, `film f` for a table | | NULL | "Unknown" or "missing". Not zero, not empty text | | Aggregate | A function over many rows: `COUNT`, `SUM`, `AVG`, `MIN`, `MAX` | | Join | Rows from two tables placed side by side | | Set operation | Rows from two queries stacked: `UNION`, `INTERSECT`, `EXCEPT` | | Subquery | A query inside another query | | CTE | A named subquery written with `WITH` | | Upsert | Insert a row, or update it if the key already exists | | Transaction | Statements that succeed or fail together | | Constraint | A rule the database enforces, such as `UNIQUE` or `CHECK` | | Index | A sorted structure that finds rows without reading the whole table | | Sequential scan | Reading every page of a table | | Query plan | The steps PostgreSQL picks to run a query; shown by `EXPLAIN` | | psql | PostgreSQL's command-line client | ## For AI agents You are reading the Markdown export of `/writing/database/postgresql-cheatsheet`. The web page has the same text plus interactive figures; nothing factual is missing here. The site index for agents is at `/llms.txt`. Related pages: `/writing/database/keys-and-relationships` (primary and foreign keys, `ON DELETE`), `/writing/database/acid-transactions` (why transactions matter) and `/writing/database/relational-model` (tables, rows and keys in theory). *** Adapted from the [Neon PostgreSQL Tutorial](https://neon.com/postgresql/tutorial). Added beyond it: logical query order, the `NOT IN` + NULL trap, `RIGHT JOIN`, `FILTER`, `MERGE` (15+), `random(min, max)` (17+), and modern defaults (`GENERATED … AS IDENTITY` over `SERIAL`, `TIMESTAMPTZ`, `JSONB`, `\copy` for hosted databases). The film, customer, staff and stock data are illustrative; figures are simplified teaching models. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.