A common table expression (CTE) is a named subquery in a WITH clause before the main statement: WITH name AS (SELECT ...) SELECT ... FROM name. The main query reads the CTE like a table, so nested subqueries become steps you read from top to bottom.
The CTE totals adds up each customer's orders, and the main query keeps the customers above 100: cy with 300 and ana with 180. A CTE exists only for the statement it belongs to.
Chain several CTEs
Separate CTEs with commas. Each one can read the CTEs defined before it.
average reads totals, and the main query reads both. The customer totals are 180, 70 and 300 and their average is about 183, so only cy is above it.
Walk a tree with WITH RECURSIVE
WITH RECURSIVE lets a CTE refer to itself. It has two parts joined by UNION ALL:
- the anchor query, which returns the starting rows;
- the recursive query, which joins the CTE to the table to find the next level.
PostgreSQL runs the recursive part on the rows the previous step produced, and stops when a step returns no rows.
Each step finds the people who report to the rows from the step before. Mia ends up at depth 3 with the path Rosa > Omar > Ivo > Mia. To list everyone under one manager instead of the whole tree, change the anchor's WHERE to id = 2.
To walk upward, from an employee to the top, swap the join condition to s.id = c.manager_id.
Stop an infinite recursive CTE
A recursive CTE ends only when a step returns nothing. With data that loops (A reports to B, B reports to A), it never ends. Add a depth limit such as WHERE c.depth < 20, or use the CYCLE clause (PostgreSQL 14+), which marks the first repeated row and stops following it:
WITH RECURSIVE chain AS (
SELECT id, manager_id FROM staff WHERE id = 5
UNION ALL
SELECT s.id, s.manager_id FROM staff s JOIN chain c ON s.id = c.manager_id
) CYCLE id SET is_cycle USING visited
SELECT * FROM chain;
Generate rows with a recursive CTE
The recursive part does not need a table. This counts from 1 to 5 by feeding each row's value back in:
For plain number or date series, generate_series is shorter.
Move rows with a data-modifying CTE
A CTE can be an INSERT, UPDATE or DELETE with a RETURNING clause, and the main statement reads the returned rows. This moves old orders into an archive table in one statement:
Orders 1 and 3 are deleted and inserted into the archive together, so a failure leaves both tables as they were. All parts of the statement see the same snapshot: the main query does not see the DELETE's effect on orders, only what RETURNING passes on (see RETURNING).
Control inlining with MATERIALIZED
Since PostgreSQL 12, a CTE without side effects that the main query references once is inlined. The planner optimizes it together with the outer query, so a WHERE written outside the CTE can still use an index. A CTE referenced two or more times is computed once and its result reused. You can force either behavior:
WITH recent AS MATERIALIZED (
SELECT * FROM events WHERE created_at > now() - interval '1 day'
)
SELECT * FROM recent WHERE user_id = 42;
WITH recent AS NOT MATERIALIZED (...)
MATERIALIZED computes the CTE once, on its own, and keeps the outer WHERE out of it. Use it when the inlined plan turns out slower. NOT MATERIALIZED asks for inlining even when the CTE is referenced more than once. Recursive and data-modifying CTEs are always computed separately.
Frequently Asked Questions
Is a CTE faster than a subquery in PostgreSQL?
Usually neither is faster. Since PostgreSQL 12 a CTE that is referenced once and has no side effects is inlined into the main query, so it gets the same plan as the equivalent subquery. Before version 12 every CTE was computed separately, which could block index use.
Can I use a CTE in an UPDATE or DELETE?
Yes. WITH can precede INSERT, UPDATE, DELETE and MERGE, and the CTE itself can be an INSERT, UPDATE or DELETE with RETURNING, whose rows the main statement reads.
How do I stop an infinite loop in a recursive CTE?
Give the recursive part a condition that eventually returns no rows, such as a depth limit (WHERE depth < 10). For data that can loop back on itself, the CYCLE clause (PostgreSQL 14+) marks a repeated row and stops there.
Can a CTE be used in more than one query?
No. A CTE exists only for the statement it is written in. To reuse a query across statements, create a view or a temporary table.