postgres-syntax-cte-recursive

Quick Reference :

Common Table Expressions (WITH name AS (...)) factor a query into named, ordered, readable stages. Three flavors :

1. Non-recursive CTE : WITH a AS (SELECT ...) SELECT ... FROM a. Equivalent to a subquery in the FROM clause, but named and reusable.
2. Recursive CTE : WITH RECURSIVE a AS (anchor UNION ALL recur). Used for tree-walks, graph traversal, generated sequences, and any "expand until done" computation.
3. Data-modifying CTE : WITH a AS (INSERT/UPDATE/DELETE ... RETURNING *) SELECT FROM a. Lets a single statement perform DML and surface the affected rows for downstream use, with all CTEs sharing one snapshot.

Two facts to internalize before writing any CTE :

• v12 behavior change: non-recursive CTEs are inlined by default (NOT MATERIALIZED) when referenced once and side-effect-free. Pre-v12 they were always materialized (optimization fence). If your code relies on the fence to prevent the planner from re-evaluating an expensive function or unsafe ordering, write WITH x AS MATERIALIZED (...) explicitly.
• Recursive CTEs MUST terminate. The recursive branch must produce zero rows eventually, or PostgreSQL loops until the work-table exhausts memory.

Minimum-viable non-recursive CTE :