CTEs and Recursive Queries
A common table expression (CTE), introduced with WITH, names a subquery for use by the main statement. CTEs can improve readability and support multi-step transformations. Modern PostgreSQL may inline eligible CTEs; MATERIALIZED and NOT MATERIALIZED can influence planning in specific cases, so do not assume every CTE is always an optimization fence.
Recursive CTEs use WITH RECURSIVE to traverse hierarchical or graph-like data. A recursive query has a non-recursive anchor and a recursive term that references prior results. Ensure the recursion terminates, and consider cycle handling for data that may contain loops.
Recursive hierarchy traversal
WITH RECURSIVE team_tree AS (
SELECT id, name, manager_id, 0 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, t.depth + 1
FROM employees AS e
JOIN team_tree AS t ON e.manager_id = t.id
)
SELECT id, name, depth
FROM team_tree
ORDER BY depth, id;💡 The anchor selects top-level employees. The recursive term finds employees whose manager is already in the tree, incrementing depth as it descends.
Key Takeaways
- CTEs name subqueries and can improve readability.
- Recursive CTEs traverse hierarchies and graph-like relationships.
- Design recursion with termination and cycle behavior in mind.
Knowledge Check
What keyword enables a recursive CTE?