Postgres CTEs (WITH Queries): Readable SQL, Recursion and Data-Modifying CTEs

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:

1. The anchor (first SELECT) produces the starting rows.
2. The recursive part joins back to the rows found so far to find the next level.
3. 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