Recursive CTEs
On This Lesson 4 topics
This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.
In this lesson
walk a hierarchy such as the category tree.
Planned topics
- Shape: anchor query
UNION ALLrecursive query that reads the CTE itself. - The recursion stops when a step returns no new rows.
- Carry a
depthorpathcolumn to order output and to detect cycles. - Bad data with a cycle loops forever; guard with a depth limit or the
CYCLEclause (PostgreSQL 14+; verify version before recording).
Draft outline details
Demonstration
WITH RECURSIVE tree AS (
SELECT id, parent_id, name, 0 AS depth FROM category WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, c.name, t.depth + 1
FROM category c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth, name;Check your understanding
What are the two parts of a recursive CTE?
Show answer
An anchor query and a recursive query joined with UNION ALL.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.