Common table expressions and recursive queries with WITH

What it is

WITH name AS (SELECT ...) defines a temporary named result for the main query; a statement can define several, each able to refer to earlier ones. The PostgreSQL documentation describes the recursive form: WITH RECURSIVE t AS (non-recursive term UNION [ALL] recursive term) evaluates the non-recursive term, puts its rows in a working table, then repeatedly evaluates the recursive term with the working table substituted for the self-reference until no rows come back. UNION discards duplicate rows, UNION ALL keeps them. The same page documents SEARCH DEPTH FIRST | BREADTH FIRST BY ... SET col to order the output and CYCLE col SET is_cycle USING path to stop on loops, and states that a non-recursive, side-effect-free WITH query referenced once is folded into the parent query by default, while MATERIALIZED forces separate evaluation and NOT MATERIALIZED forces folding.

Why it matters

Hierarchies (categories, org charts, threaded comments, parts explosions) do not fit a fixed number of joins. A recursive CTE handles arbitrary depth in one statement; non-recursive CTEs split a long query into named steps that can be read and tested one at a time.

How to apply

• Ancestor chain: start from the node (SELECT id, parent_id, 1 AS depth FROM nodes WHERE id = $1) and join the recursive term on parent_id; descendants go the other way. Carry depth and cap it (WHERE depth < 50) as a guard against unexpected loops.
• For graphs that may contain cycles, use UNION or the CYCLE clause; UNION ALL on a cycle never terminates without a cap.
• Use SEARCH DEPTH FIRST BY name SET ord with ORDER BY ord to print a tree in nested order.
• Data-modifying CTEs move rows in one statement: WITH moved AS (DELETE FROM live WHERE ... RETURNING *) INSERT INTO archive SELECT * FROM moved.
• A CTE referenced more than once is materialised by default; write MATERIALIZED on a single-use CTE only to force separate evaluation (an expensive function computed once), and NOT MATERIALIZED on a multiply referenced one when each use needs only a small, index-friendly slice, as the documentation's big_table example shows.

Pitfalls

A materialised CTE is evaluated as written, so the outer WHERE cannot be pushed into it and base-table indexes do not serve the outer predicate. Every row a recursive term produces stays in the result, so wide rows in large trees are costly. The documentation calls the breadth-first output order an implementation detail not to be relied on and the order within a level undefined; use SEARCH or an explicit ORDER BY.