"Recursive CTEs in PostgreSQL: Org Charts to Comment Threads"
"postgresql recursive cte tutorial — WITH RECURSIVE queries for org charts, comment threads, and friend-of-friend graphs, with cycle detection, depth limits, and the infinite-loop fix."
Every app eventually needs a tree: employee → manager chains, nested comment replies, category breadcrumbs, follower graphs. The naive approach — one query per level, looping in application code — turns a single page load into a dozen round trips. This postgresql recursive cte tutorial shows the alternative: one WITH RECURSIVE query that walks the whole tree in the database, with cycle protection and depth limits so a bad row can't hang your query forever. Last updated: October 4, 2026 What you'll learn The anatomy of WITH RECURSIVE : anchor member, recursive member, termination Walking an org chart: every employee under a manager, with depth Nested comment threads: replies in tree order with path sorting Friend-of-friend queries on a follower graph Cycle detection with path arrays so graphs can't loop forever Depth limits and the infinite-loop fix every recursive CTE needs UNION vs UNION ALL inside recursion (and why the answer matters) How WITH RECURSIVE Actually Works A recursive CTE has exactly three parts, and the name RECURSIVE is mandatory in PostgreSQL — without it, the query won't compile. WITH RECURSIVE employee_tree AS ( -- 1. ANCHOR MEMBER: the starting rows (runs once) SELECT id, name, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL -- the CEO: no manager UNION ALL -- 2. RECURSIVE MEMBER: joins back to the CTE itself SELECT e.id, e.name, e.manager_id, t.depth + 1 FROM employees e JOIN employee_tree t ON e.manager_id = t.id -- 3. TERMINATION: the recursion stops when the JOIN finds no new rows ) SELECT * FROM employee_tree; Think of it as a loop the database runs for you: the anchor produces the first batch of rows, the recursive member feeds those rows back in to find the next level, and it repeats until an iteration…