38 Common Table Expressions (WITH)

A common table expression (CTE) is a named subquery introduced with WITH name AS (SELECT ...). You can refer to it from the main query (and from later CTEs) as if it were a table. CTEs make complex queries readable: each CTE has a name and a job, and the main query reads top-down like a story. PostgreSQL also supports recursive CTEs (WITH RECURSIVE), which are how you walk trees, graphs, and chains in pure SQL.

What you’ll learn

• Defining a CTE with WITH name AS (...)
• Chaining multiple CTEs in one query
• Data-modifying CTEs: INSERT, UPDATE, DELETE inside WITH
• Recursive CTEs for hierarchies and graphs
• The performance trade-offs (MATERIALIZED / NOT MATERIALIZED)

Example

-- A single CTE
WITH spend AS (
  SELECT customer_id, sum(total) AS total_spent
  FROM orders_example
  GROUP BY customer_id
)
SELECT c.name, COALESCE(s.total_spent, 0) AS spent
FROM customers_example c
LEFT JOIN spend s ON s.customer_id = c.id
ORDER BY c.id;

-- Multiple CTEs, each named and easy to read
WITH per_customer AS (
  SELECT customer_id, sum(total) AS total_spent, count(*) AS order_count
  FROM orders_example
  GROUP BY customer_id
),
big_spenders AS (
  SELECT * FROM per_customer WHERE total_spent > 30
)
SELECT c.name, b.total_spent, b.order_count
FROM big_spenders b
JOIN customers_example c ON c.id = b.customer_id
ORDER BY b.total_spent DESC;

-- Data-modifying CTE: archive then return what was archived
DROP TABLE IF EXISTS orders_archive;
CREATE TABLE orders_archive (LIKE orders_example INCLUDING ALL);

WITH archived AS (
  DELETE FROM orders_example
  WHERE total < 20
  RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM archived
RETURNING id;

-- RECURSIVE CTE: walk an employee hierarchy
WITH RECURSIVE reports AS (
  -- anchor: top-level employees (no manager)
  SELECT id, name, manager_id, 1 AS depth
  FROM employees_example
  WHERE manager_id IS NULL
  UNION ALL
  -- recursive step: rows whose manager appears in the previous level
  SELECT e.id, e.name, e.manager_id, r.depth + 1
  FROM employees_example e
  JOIN reports r ON e.manager_id = r.id
)
SELECT depth, name FROM reports ORDER BY depth, id;

A CTE is just a named subquery. You can have many, comma-separated, each able to reference earlier ones. Read top-to-bottom, each step a clear chunk. Compared to a deeply nested set of derived tables, CTEs are usually easier to follow and easier to change.

Data-modifying CTEs are a PostgreSQL specialty: an INSERT, UPDATE, or DELETE with RETURNING can be the body of a CTE, and its returned rows flow into the next statement — atomically, in one round trip. The “delete from one table, insert into another” archive pattern shown above is the canonical example.

Recursive CTEs have two halves combined with UNION ALL: an anchor query that produces the initial rows, and a recursive query that references the CTE by name to add more rows. PostgreSQL keeps iterating until no new rows are produced. Use them for hierarchies (employees → manager), graph traversal (find all transitive dependencies), and series generators (numbers, dates).

Run the example

To run (requires tables from the earlier lessons):

$ psql -f source/cte.sql postgres
 name  | spent
-------+-------
 Alice | 80.00
 Bob   |  0.00
 Carol |  0.00
(3 rows)
 ...
 depth | name
-------+-------
     1 | Alice
     2 | Bob
     2 | Carol
     3 | Dave
     3 | Eve
(5 rows)

Common pitfalls

• A nonrecursive, side-effect-free SELECT CTE referenced once is, in PostgreSQL 12+, normally folded into its parent query — the planner treats it like a subquery. If you want the older “always materialize” behavior, write WITH x AS MATERIALIZED (...).
• WITH RECURSIVE accepts both UNION ALL and UNION. UNION eliminates duplicate complete rows, including rows produced earlier; UNION ALL keeps them. Choose based on the recursion and its termination condition.
• A recursive CTE that does not converge (the recursive step keeps adding rows) is an infinite loop. The query may run indefinitely, spill to disk, or fail; work_mem is not an execution limit. Add a termination condition (WHERE depth < 100 etc.) when in doubt.
• Data-modifying CTEs inside WITH are evaluated in unspecified order relative to each other — do not rely on side effects between them. The main statement shares the same snapshot and has no guaranteed modification order relative to them. Pass data through RETURNING rather than relying on visible side effects.

Downloads

SQL file: cte.sql · All lesson files · Setup guide