Postgres CTEs (WITH Queries): Readable SQL, Recursion and Data-Modifying CTEs
Common table expressions let you name subqueries and build complex SQL step by step. Basic WITH queries, recursive CTEs for trees and hierarchies, data-modifying CTEs that insert or delete and return rows, MATERIALIZED vs NOT MATERIALIZED, and when a CTE affects performance.
A common table expression (CTE) is a named, temporary result you define at the start of a query with WITH, then use like a table. It turns a tangle of nested subqueries into readable steps. (SQL for beginners)
The basic shape
WITH paid_orders AS (
SELECT customer_id, total
FROM orders
WHERE status = 'paid'
AND created_at >= now() - interval '30 days'
),
customer_totals AS (
SELECT customer_id, SUM(total) AS spent
FROM paid_orders
GROUP BY customer_id
)
SELECT c.email, t.spent
FROM customer_totals t
JOIN customers c ON c.id = t.customer_id
WHERE t.spent > 100
ORDER BY t.spent DESC;
Each CTE reads like a step in an explanation: "take paid orders from the last 30 days, total them per customer, show the big spenders". (SQL GROUP BY, SQL joins)
Later CTEs can refer to earlier ones. The final SELECT uses any of them.
Recursive CTEs: trees and hierarchies
WITH RECURSIVE lets a CTE refer to itself — perfect for categories with parent categories, comment threads, org charts, or folder structures.
-- every category under "Electronics", with depth
WITH RECURSIVE tree AS (
SELECT id, name, parent_id, 0 AS depth
FROM categories
WHERE name = 'Electronics'
UNION ALL
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) || name AS category
FROM tree
ORDER BY depth, name;
How it runs:
- The anchor (first
SELECT) produces the starting rows. - The recursive part joins back to the rows found so far to find the next level.
- It repeats until no new rows appear.
Guard against cycles in messy data — Postgres 14+ has a CYCLE clause, or track visited IDs in an array and stop when you revisit one. A depth limit (WHERE t.depth < 20) is a simple safety net.
Recursive CTEs also generate series and walk graphs (though for dates, generate_series() is simpler).
Data-modifying CTEs
In Postgres, a CTE can contain INSERT, UPDATE or DELETE with RETURNING, and the rest of the query can use the returned rows — all in one statement, one transaction:
-- archive and delete old sessions in one atomic step
WITH deleted AS (
DELETE FROM sessions
WHERE last_seen < now() - interval '90 days'
RETURNING *
)
INSERT INTO sessions_archive
SELECT * FROM deleted;
-- create an order and its first line item together
WITH new_order AS (
INSERT INTO orders (customer_id) VALUES (42)
RETURNING id
)
INSERT INTO order_items (order_id, product_id, quantity)
SELECT id, 7, 1 FROM new_order;
All parts see the same snapshot of the data and either all succeed or all fail. (Database transactions, Postgres upsert)
Performance: MATERIALIZED or not
Before Postgres 12, every CTE was computed once and stored ("an optimisation fence"), which sometimes made queries slower because filters couldn't be pushed inside. Since Postgres 12, a CTE that's referenced once and has no side effects is usually inlined like a subquery.
You can control it:
WITH big AS MATERIALIZED (...) -- compute once, reuse (good if referenced many times and expensive)
WITH filtered AS NOT MATERIALIZED (...) -- inline (good when outer WHERE should apply inside)
When a query with CTEs is slow, check the plan with EXPLAIN ANALYZE before guessing. (Reading EXPLAIN ANALYZE)
CTE vs subquery vs temporary table vs view
| Use when | |
|---|---|
| CTE | Breaking one query into readable steps; recursion; atomic multi-step writes |
| Subquery | A small inline piece |
| Temp table | Reusing an intermediate result across several queries in a session |
| View | Reusing the same query across your app (Materialized views) |
In ORMs
Most ORMs support CTEs (Drizzle's $with, Prisma via raw queries, SQLAlchemy's .cte()), and for complex reports, writing the SQL directly is often clearer. (What is an ORM?)
EasySpawn gives each app its own Postgres database and a terminal with Claude Code, which is good at turning a tangled report query into readable CTE steps — and checking the plan afterwards. See how it works or join the waitlist.
Related: Postgres Window Functions · SQL GROUP BY Explained · Reading Postgres EXPLAIN ANALYZE · Postgres Upsert
Keep reading
UUIDv7 Explained: Time-Ordered IDs and Why Postgres 18 Added uuidv7()
UUIDv7 is a UUID that starts with a millisecond timestamp, so new IDs sort in creation order. Why that makes database indexes faster than random UUIDv4, the format in detail, generating them in Postgres 18, Node.js and Python, what the timestamp leaks, and when to still use v4 or bigint.
SQLite WAL Mode: Faster Writes and Readers That Don't Block
Write-ahead logging (WAL) mode is the single most important setting for using SQLite in a web app: readers no longer block the writer, writes get faster, and "database is locked" errors mostly disappear. How to turn it on, the companion pragmas, checkpoints, the -wal and -shm files, safe backups, and its limits.