PostgreSQL CTE: WITH queries explained

Quick answer

A common table expression names a subquery so the main query stays readable:

WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at >= now() - interval '30 days'
)
SELECT customer_id, count(*)
FROM recent_orders
GROUP BY customer_id;

The CTE exists only for the duration of that statement.

Basic syntax

WITH name AS (
  subquery
)
SELECT ... FROM name;

Chain several, and later ones can reference earlier ones:

WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at >= now() - interval '30 days'
),
order_totals AS (
  SELECT customer_id, sum(amount) AS total
  FROM recent_orders
  GROUP BY customer_id
)
SELECT c.name, t.total
FROM order_totals t
JOIN customers c ON c.id = t.customer_id
WHERE t.total > 1000
ORDER BY t.total DESC;

Note the comma between CTEs, and that WITH appears only once. Reading that top to bottom describes the pipeline, which is the whole reason to use them.

CTEs are no longer an optimization fence

This is the thing most articles on the subject still get wrong, because it changed in PostgreSQL 12.

Before 12, PostgreSQL always materialized a CTE: it ran the subquery to completion, stashed the result, then used it. That meant a CTE could not be optimized together with the outer query, and a filter outside the CTE could not be pushed inside it. People used this deliberately as a planner hint, and other people got badly bitten by it.

From PostgreSQL 12, a CTE that is referenced once and has no side effects is inlined, exactly like a subquery.

You can see it. With a 50,000-row table indexed on id:

EXPLAIN WITH c AS (SELECT * FROM big) SELECT * FROM c WHERE id = 42;

->  Bitmap Index Scan on big_id_idx  (cost=0.00..6.17 rows=250 width=0)
      Index Cond: (id = 42)

The WHERE id = 42 got pushed into the CTE and used the index. On PostgreSQL 11 this would have scanned all 50,000 rows first.

Force the old behaviour with MATERIALIZED:

EXPLAIN WITH c AS MATERIALIZED (SELECT * FROM big) SELECT * FROM c WHERE id = 42;

CTE Scan on c  (cost=756.00..1881.00 rows=250 width=8)
  ->  Seq Scan on big  (cost=0.00..756.00 rows=50000 width=8)

Sequential scan of the whole table, then filter. That is the fence, back on request.

When to use MATERIALIZED

Inlining is the right default, but there are cases where you want the fence:

• The CTE is expensive and referenced several times. Inlining means running it once per reference. Materializing runs it once.
• The subquery has side effects, or calls a volatile function you want evaluated exactly once.
• You know better than the planner, which is rarer than it feels, but does happen with bad estimates.

NOT MATERIALIZED forces inlining even when a CTE is referenced multiple times.

A CTE that is referenced more than once is materialized by default, so the common case is already handled.

Recursive CTEs

WITH RECURSIVE is how you walk a tree or a graph. It is also the part people find genuinely hard, so it is worth taking slowly.

The shape is always the same:

WITH RECURSIVE name AS (
  -- base case: where to start
  SELECT ...
  UNION ALL
  -- recursive case: how to get the next level, referencing `name`
  SELECT ... FROM name JOIN ...
)
SELECT * FROM name;

A concrete example. Given an employee table where each row points at a manager:

CREATE TABLE employees (
  id        int PRIMARY KEY,
  name      text,
  manager_id int REFERENCES employees(id)
);

INSERT INTO employees VALUES
  (1, 'Ada',  NULL),
  (2, 'Grace', 1),
  (3, 'Alan',  2),
  (4, 'Edsger', 2);

Find everyone under Ada, with their depth:

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, org.depth + 1
  FROM employees e
  JOIN org ON e.manager_id = org.id
)
SELECT depth, name FROM org ORDER BY depth, name;

 depth |  name
-------+--------
     1 | Ada
     2 | Grace
     3 | Alan
     3 | Edsger

What happens mechanically: the base case runs once and produces Ada. The recursive term then runs against only the rows produced by the previous round, not the whole accumulated set. It repeats until a round returns nothing.

That last point is the one that clears up most confusion. org inside the recursive term does not mean "everything so far", it means "what the last iteration produced".

Generating a series

Recursion is not only for trees:

WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;

For this specific job generate_series(1, 5) is simpler and faster. But the pattern generalizes to things generate_series cannot do.

Guarding against infinite loops

If your data has a cycle, UNION ALL will recurse forever. Two defences.

UNION instead of UNION ALL deduplicates, which stops a loop that revisits identical rows, at the cost of a distinct on every round.

A depth limit is blunter and more predictable:

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, org.depth + 1
  FROM employees e
  JOIN org ON e.manager_id = org.id
  WHERE org.depth < 10
)
SELECT * FROM org;

For real cycle detection, track the path:

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, ARRAY[id] AS path
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, org.path || e.id
  FROM employees e
  JOIN org ON e.manager_id = org.id
  WHERE NOT e.id = ANY(org.path)
)
SELECT * FROM org;

PostgreSQL 14 added a CYCLE clause that does this for you:

WITH RECURSIVE org AS (...)
  CYCLE id SET is_cycle USING path
SELECT * FROM org;

Note that RECURSIVE goes after WITH, once, even if only one of several CTEs is actually recursive.

Data-modifying CTEs

INSERT, UPDATE, and DELETE can live inside a CTE, which is how you move rows between tables in one statement:

WITH archived AS (
  DELETE FROM orders
  WHERE created_at < now() - interval '1 year'
  RETURNING *
)
INSERT INTO orders_archive SELECT * FROM archived;

The RETURNING is what makes this work: it hands the deleted rows to the next stage.

Two rules that matter here.

Data-modifying CTEs always run, whether or not the outer query references them. They are never inlined and never skipped.

They all see the same snapshot. Sub-statements cannot see each other's changes. So this does not do what it looks like:

WITH inserted AS (
  INSERT INTO t VALUES (1) RETURNING *
)
SELECT * FROM t;   -- does NOT include the new row

The SELECT reads the table as it was before the statement began. To see the new rows, select from the CTE, not the table.

The execution order of several data-modifying CTEs is also not defined. If two of them touch the same row, the outcome is unpredictable. Do not rely on ordering.

CTE vs subquery vs view

Since 12, a single-use CTE and an equivalent subquery produce the same plan, so the choice is about readability. Use a CTE when naming the step explains the query. Use a view when several queries need the same step.

Common problems

relation "my_cte" does not exist. A CTE only exists for its own statement. You cannot reference it in a later query, and you cannot reference a CTE from a sibling CTE defined after it.

My CTE got slower on an upgrade. You relied on the pre-12 fence. Add MATERIALIZED to get it back.

My recursive CTE runs forever. A cycle in the data. Add a depth limit, use UNION, or track the path.

ERROR: recursive reference to query "x" must not appear more than once. The recursive term can only mention the CTE once. Restructure the join.

Quick reference