Recursive CTEs: Hierarchies, Graphs & Trees
Traverse org charts, category trees, and graphs using recursive Common Table Expressions.
🎯 What You'll Learn
- Recursive CTE anatomy: anchor member + recursive member
- Org chart traversal and reporting chains
- Graph shortest-path and bill-of-materials queries
- Category trees with breadcrumbs and materialized paths
- Cycle detection, depth limits, and performance tips
🔄 How Recursive CTEs Work
A recursive CTE is like Russian nesting dolls — it calls itself repeatedly until a stopping condition is met. The anchor is the outermost doll (starting point), and each recursive step opens the next smaller doll inside.
Recursive CTE Basics
Counting, date series, and Fibonacci
-- Recursive CTE structure:
-- 1. ANCHOR member: the starting point (base case)
-- 2. RECURSIVE member: refers back to itself
-- 3. UNION ALL: combines anchor + recursive results
-- Simple counting example:
WITH RECURSIVE counter AS (
-- Anchor: start at 1
SELECT 1 AS n
UNION ALL
-- Recursive: add 1 each iteration
SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;
-- Returns: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
-- Generate a date series (when generate_series isn't av
...👥 Org Chart Hierarchies
The most common use case: traversing an employee → manager tree. Start from the CEO (anchor), then recursively find everyone who reports to each level.
💡 Pro Tip — Path Column for Sorting
Building a path column (array or string) during recursion lets you ORDER BY it to get correct tree ordering. Without it, results appear in arbitrary order.
Org Chart Traversal
Navigate employee hierarchies and count reports
-- Employee-Manager hierarchy (most common use case)
-- Table: employees (id, name, manager_id)
-- Find all reports under a specific manager (org chart)
WITH RECURSIVE org_chart AS (
-- Anchor: the top-level manager
SELECT id, name, manager_id, 0 AS depth,
name::text AS path
FROM employees
WHERE id = 1 -- CEO
UNION ALL
-- Recursive: find direct reports of each level
SELECT e.id, e.name, e.manager_id, oc.depth + 1,
oc.path || ' → ' || e.name
...🕸️ Graph Traversal
Recursive CTEs can traverse arbitrary graphs — finding paths between nodes, computing shortest routes, or exploding bills of materials. The key is preventing infinite cycles.
⚠️ Common Mistake — Forgetting Cycle Prevention
Without cycle detection, a graph with cycles (A → B → C → A) causes infinite recursion. Always track visited nodes in an array and check WHERE NOT node = ANY(path).
Graph & BOM Traversal
Shortest paths and bill-of-materials explosion
-- Graph traversal: find all paths between nodes
-- Table: edges (from_node, to_node, weight)
-- Find shortest path from node A to node F
WITH RECURSIVE paths AS (
-- Anchor: start from node A
SELECT
from_node,
to_node,
weight AS total_cost,
ARRAY[from_node, to_node] AS path,
1 AS hops
FROM edges
WHERE from_node = 'A'
UNION ALL
-- Recursive: extend paths by one edge
SELECT
p.from_node,
e.to_node,
p.to
...🌳 Category Trees
E-commerce categories, file system folders, menu structures — all are trees. Recursive CTEs build breadcrumbs, compute depth, and find ancestors or descendants from any node.
Category Tree Traversal
Breadcrumbs, tree ordering, and ancestor lookup
-- Category tree with materialized path
-- Useful for e-commerce product categories
WITH RECURSIVE category_tree AS (
-- Anchor: root categories (no parent)
SELECT id, name, parent_id, 0 AS depth,
ARRAY[id] AS id_path,
name::text AS breadcrumb
FROM categories
WHERE parent_id IS NULL
UNION ALL
-- Recursive: child categories
SELECT c.id, c.name, c.parent_id, ct.depth + 1,
ct.id_path || c.id,
ct.breadcrumb || ' > ' || c.name
...🛡️ Safety & Performance
Recursive queries can run forever without safeguards. Always include cycle detection and depth limits. PostgreSQL 14+ has built-in CYCLE and SEARCH clauses.
Safety & Performance
Cycle detection, depth limits, and indexing
-- Recursive CTE safety and performance
-- 1. CYCLE DETECTION (PostgreSQL 14+)
WITH RECURSIVE traversal AS (
SELECT id, parent_id, name, ARRAY[id] AS path
FROM nodes WHERE id = 1
UNION ALL
SELECT n.id, n.parent_id, n.name, t.path || n.id
FROM nodes n
JOIN traversal t ON n.parent_id = t.id
WHERE NOT n.id = ANY(t.path) -- manual cycle prevention
)
SELECT * FROM traversal;
-- PostgreSQL 14+ has built-in CYCLE detection:
-- WITH RECURSIVE traversal AS (...)
-- CYCLE id
...📋 Quick Reference
| Use Case | Pattern |
|---|---|
| Org chart | JOIN on manager_id = id |
| Category tree | JOIN on parent_id = id |
| Graph path | JOIN edges + cycle check |
| Date series | d + INTERVAL '1 day' |
| BOM explosion | Multiply quantities down tree |
🎉 Lesson Complete!
You can now traverse any hierarchy, tree, or graph with recursive CTEs. Next, explore temporal tables and time-travel queries!
Sign up for free to track which lessons you've completed and get learning reminders.