A Common Table Expression names a subquery up front, which makes complex queries readable top-to-bottom instead of inside-out.
SQL
WITH author_stats AS (
SELECT author_id, COUNT(*) AS book_count, AVG(price) AS avg_price
FROM books
GROUP BY author_id
)
SELECT a.name, s.book_count, ROUND(s.avg_price, 2)
FROM author_stats s
JOIN authors a ON a.id = s.author_id
WHERE s.book_count > 2
ORDER BY s.book_count DESC;Several CTEs
Each can reference the ones before it:
SQL
WITH
recent AS (
SELECT * FROM books WHERE published >= '2020-01-01'
),
by_author AS (
SELECT author_id, COUNT(*) AS n FROM recent GROUP BY author_id
)
SELECT a.name, b.n
FROM by_author b
JOIN authors a ON a.id = b.author_id
ORDER BY b.n DESC;Data-modifying CTEs
Postgres lets INSERT/UPDATE/DELETE appear in a CTE — useful for
"move rows between tables atomically":
SQL
WITH deleted AS (
DELETE FROM books
WHERE published < '2000-01-01'
RETURNING *
)
INSERT INTO archived_books
SELECT * FROM deleted;Recursive CTEs
For hierarchies — category trees, org charts, threaded comments:
SQL
WITH RECURSIVE tree AS (
-- anchor: the starting rows
SELECT id, name, parent_id, 1 AS depth
FROM categories
WHERE parent_id IS NULL
UNION ALL
-- recursive part: children of what we already have
SELECT c.id, c.name, c.parent_id, t.depth + 1
FROM categories c
JOIN tree t ON c.parent_id = t.id
)
SELECT repeat(' ', depth - 1) || name AS indented, depth
FROM tree
ORDER BY depth, name;Walking a tree in application code means one query per level; a recursive CTE does it in a single round trip.
Add a depth guard (WHERE t.depth < 10) if the data might contain a
cycle, or the query will never finish.
A note on performance
Before Postgres 12, CTEs were always materialised (an optimisation fence). From 12 onward they can be inlined. You can force either behaviour:
SQL
WITH stats AS MATERIALIZED (...) -- compute once
WITH stats AS NOT MATERIALIZED (...) -- allow inlining