What a CTE Is and Why It Helps Readability
A Common Table Expression, introduced with the WITH keyword, lets you name an intermediate result set and reference it later in the same statement as if it were a table, letting you break a deeply nested subquery into a sequence of clearly named steps. Since PostgreSQL 12, non-recursive CTEs are inlined into the outer query by default (the planner can 'flatten' them like a subquery) unless they are referenced multiple times, marked MATERIALIZED, or are recursive, so modern CTEs generally do not carry the fixed optimization-fence penalty older PostgreSQL versions had.
Cricket analogy: Naming a specific fielding formation 'attacking cordon' and referring back to it in team talks throughout the match, rather than re-describing all five slip positions every time, is what a CTE does for a repeated subquery expression.
MATERIALIZED and NOT MATERIALIZED
Since PostgreSQL 12, you can override the default inlining behavior explicitly: WITH cte AS MATERIALIZED (...) forces the CTE to be computed once and stored, acting as an optimization fence just like pre-12 CTEs always did, which is useful when the CTE is expensive and referenced multiple times, or when it deliberately isolates a side-effecting data-modifying statement (INSERT/UPDATE/DELETE ... RETURNING) inside the WITH clause. WITH cte AS NOT MATERIALIZED forces inlining even in cases the planner might otherwise choose to materialize, useful when you know the CTE is cheap and want the outer query's WHERE clauses pushed down into it for a better plan.
Cricket analogy: Deciding to pre-record a specific fielding drill on video (MATERIALIZED) because the whole squad will rewatch it many times, versus just calling out instructions live each time (NOT MATERIALIZED) because it's a one-off tweak, mirrors the CTE materialization choice.
Recursive CTEs for Hierarchical Data
WITH RECURSIVE defines a CTE with two parts unioned together: a non-recursive base case that seeds the initial rows, and a recursive term that references the CTE's own name and is repeatedly evaluated against the previous iteration's output until it returns no new rows, making it the standard PostgreSQL tool for traversing trees such as an employee-manager hierarchy, a bill-of-materials, or a category tree. Because the recursive term must terminate, cyclic data (e.g. a manager accidentally reporting to their own subordinate) can cause an infinite loop, which is why production recursive CTEs typically track a visited-nodes array and add a WHERE NOT (id = ANY(path)) guard or a UNION (not UNION ALL) to deduplicate and break cycles.
Cricket analogy: Tracing a bowling lineage, a fast bowler who was mentored by a former pacer who was in turn mentored by another, iteration by iteration until you hit the founding mentor with no predecessor, mirrors how a recursive CTE walks up an employee-manager hierarchy.
-- Simple readability CTE, inlined by the planner (PG 12+) since referenced once
WITH recent_orders AS (
SELECT customer_id, total_amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT customer_id, SUM(total_amount) AS total_spent
FROM recent_orders
GROUP BY customer_id
ORDER BY total_spent DESC;
-- Recursive CTE walking an employee-manager hierarchy, with a cycle guard
WITH RECURSIVE org_chain AS (
SELECT employee_id, manager_id, full_name, 1 AS depth,
ARRAY[employee_id] AS path
FROM employees
WHERE employee_id = 42 -- start from a specific employee
UNION ALL
SELECT e.employee_id, e.manager_id, e.full_name, oc.depth + 1,
oc.path || e.employee_id
FROM employees e
JOIN org_chain oc ON e.employee_id = oc.manager_id
WHERE NOT (e.employee_id = ANY(oc.path)) -- guard against cycles
)
SELECT depth, full_name FROM org_chain ORDER BY depth;
-- Isolating a data-modifying statement inside a CTE
WITH archived AS MATERIALIZED (
DELETE FROM sessions WHERE expires_at < now()
RETURNING session_id, user_id
)
INSERT INTO session_audit_log (session_id, user_id, archived_at)
SELECT session_id, user_id, now() FROM archived;You can force a CTE to break the automatic inlining optimization by writing WITH cte AS MATERIALIZED (...). This is still commonly needed when a data-modifying CTE (INSERT/UPDATE/DELETE ... RETURNING) must run exactly once regardless of how the outer query references it.
CTEs vs. Subqueries vs. Views
A CTE, a subquery, and a view can all express the same logical computation, but they differ in scope and reusability: a subquery is inline and single-use within one statement, a CTE is named and can be referenced multiple times within one statement (and, since PG 12, is planned essentially like a smart subquery), and a view is a stored, named query reusable across many statements and sessions but with no per-statement materialization control. The main reasons to reach for a CTE over a plain subquery are readability for a multi-step pipeline and the ability to define recursive logic, which subqueries cannot express at all.
Cricket analogy: A one-off improvised field placement called out for a single ball is like a subquery; a named formation referenced repeatedly through one innings is like a CTE; and a standard fielding template written into the team's permanent playbook for every match is like a view.
A recursive CTE without a cycle guard on genuinely cyclic data (self-referencing foreign keys that form a loop) will run forever, consuming memory until it errors out or exhausts disk for its work files. Always add a visited-path array and a WHERE NOT (id = ANY(path)) style guard, or use the SQL standard CYCLE clause available since PostgreSQL 14, when the source data cannot guarantee acyclicity.
- A CTE, introduced with WITH, names an intermediate result for readability and optional reuse within one statement.
- Since PostgreSQL 12, non-recursive CTEs are inlined by default rather than always acting as an optimization fence.
- WITH ... AS MATERIALIZED forces single computation and storage; NOT MATERIALIZED forces inlining even when the planner might otherwise materialize.
- WITH RECURSIVE combines a base case and a recursive term, repeatedly evaluated until no new rows appear, for hierarchical traversal.
- Recursive CTEs on cyclic data need an explicit guard (visited-path array or the PG14+ CYCLE clause) to avoid infinite loops.
- CTEs uniquely support recursion and multi-step readability that plain subqueries cannot express.
- Data-modifying statements (INSERT/UPDATE/DELETE RETURNING) can be embedded in a CTE, typically as MATERIALIZED, to guarantee single execution.
Practice what you learned
1. Since PostgreSQL 12, what is the default planning behavior for a simple, non-recursive CTE referenced only once?
2. What is the primary purpose of writing WITH cte AS MATERIALIZED (...) explicitly?
3. What are the two required parts of a WITH RECURSIVE CTE?
4. Why must a recursive CTE traversing genuinely cyclic data include an explicit guard?
5. What can a CTE express that a plain subquery cannot?
Was this page helpful?
You May Also Like
Window Functions Explained
Learn how PostgreSQL's window functions compute values across a set of related rows without collapsing them, enabling rankings, running totals, and moving averages.
Reading EXPLAIN ANALYZE
Learn to interpret PostgreSQL's EXPLAIN ANALYZE output so you can tell what a query planned versus what it actually did, and use that gap to fix slow queries.
The Query Planner and Cost Estimation
Understand how PostgreSQL's planner generates candidate execution plans and estimates their cost so it can pick the cheapest one.