postgresql recursive-cte hierarchical-data cte tree-structure graph

Recursive queries and hierarchical data with CTEs

Maria Garcia Feb 2026

2 tabs

-- Create hierarchical table (org chart)
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  title VARCHAR(100),
  manager_id INT REFERENCES employees(id),
  salary DECIMAL(10,2)
);

INSERT INTO employees (id, name, title, manager_id, salary) VALUES
(1, 'Alice CEO', 'CEO', NULL, 200000),
(2, 'Bob VP', 'VP Engineering', 1, 150000),
(3, 'Carol VP', 'VP Sales', 1, 150000),
(4, 'Dave Manager', 'Engineering Manager', 2, 120000),
(5, 'Eve Manager', 'Sales Manager', 3, 120000),
(6, 'Frank Dev', 'Senior Developer', 4, 100000),
(7, 'Grace Dev', 'Developer', 4, 80000),
(8, 'Henry Sales', 'Sales Rep', 5, 70000);

-- Recursive CTE: Find all subordinates of employee 2
WITH RECURSIVE subordinates AS (
  -- Base case: Start with employee 2
  SELECT id, name, title, manager_id, 1 AS level
  FROM employees
  WHERE id = 2

  UNION ALL

  -- Recursive case: Find employees managed by previous results
  SELECT e.id, e.name, e.title, e.manager_id, s.level + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT
  id,
  REPEAT('  ', level - 1) || name AS indented_name,
  title,
  level
FROM subordinates
ORDER BY level, name;

-- Find all managers above an employee (ancestors)
WITH RECURSIVE managers AS (
  -- Base case: Start with specific employee
  SELECT id, name, title, manager_id, 0 AS level
  FROM employees
  WHERE id = 6  -- Frank

  UNION ALL

  -- Recursive case: Find manager of previous result
  SELECT e.id, e.name, e.title, e.manager_id, m.level + 1
  FROM employees e
  JOIN managers m ON e.id = m.manager_id
)
SELECT name, title, level
FROM managers
ORDER BY level DESC;

-- Full org chart from top down
WITH RECURSIVE org_chart AS (
  -- Base case: CEO (no manager)
  SELECT
    id,
    name,
    title,
    manager_id,
    1 AS level,
    name::TEXT AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: Employees at next level
  SELECT
    e.id,
    e.name,
    e.title,
    e.manager_id,
    oc.level + 1,
    oc.path || ' > ' || e.name
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT
  level,
  REPEAT('  ', level - 1) || name AS indented_name,
  title,
  path
FROM org_chart
ORDER BY path;

-- Aggregate: Total salary by department (including subordinates)
WITH RECURSIVE dept_salaries AS (
  SELECT
    id,
    name,
    salary,
    salary AS total_dept_salary
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT
    e.id,
    e.name,
    e.salary,
    e.salary + ds.total_dept_salary
  FROM employees e
  JOIN dept_salaries ds ON e.manager_id = ds.id
)
SELECT name, total_dept_salary
FROM dept_salaries
ORDER BY total_dept_salary DESC;

-- Category tree
CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  parent_id INT REFERENCES categories(id)
);

INSERT INTO categories (id, name, parent_id) VALUES
(1, 'Electronics', NULL),
(2, 'Computers', 1),
(3, 'Phones', 1),
(4, 'Laptops', 2),
(5, 'Desktops', 2),
(6, 'Smartphones', 3),
(7, 'Feature Phones', 3);

-- Find all subcategories
WITH RECURSIVE subcategories AS (
  SELECT id, name, parent_id, 1 AS level
  FROM categories
  WHERE id = 1  -- Electronics

  UNION ALL

  SELECT c.id, c.name, c.parent_id, sc.level + 1
  FROM categories c
  JOIN subcategories sc ON c.parent_id = sc.id
)
SELECT
  REPEAT('  ', level - 1) || name AS category_tree,
  level
FROM subcategories
ORDER BY level, name;

-- Find category path (breadcrumbs)
WITH RECURSIVE category_path AS (
  SELECT id, name, parent_id, name::TEXT AS path
  FROM categories
  WHERE id = 6  -- Smartphones

  UNION ALL

  SELECT c.id, c.name, c.parent_id, c.name || ' > ' || cp.path
  FROM categories c
  JOIN category_path cp ON c.id = cp.parent_id
)
SELECT path
FROM category_path
WHERE parent_id IS NULL;

-- Cycle detection (prevent infinite loops)
CREATE TABLE graph (
  from_node INT,
  to_node INT
);

INSERT INTO graph VALUES
(1, 2), (2, 3), (3, 4), (4, 2);  -- Has cycle: 2->3->4->2

WITH RECURSIVE graph_traversal AS (
  -- Base case
  SELECT
    from_node,
    to_node,
    ARRAY[from_node, to_node] AS path,
    false AS is_cycle
  FROM graph
  WHERE from_node = 1

  UNION ALL

  -- Recursive case with cycle detection
  SELECT
    g.from_node,
    g.to_node,
    gt.path || g.to_node,
    g.to_node = ANY(gt.path) AS is_cycle
  FROM graph g
  JOIN graph_traversal gt ON g.from_node = gt.to_node
  WHERE NOT gt.is_cycle  -- Stop traversing cycles
    AND array_length(gt.path, 1) < 10  -- Depth limit
)
SELECT
  path,
  CASE WHEN is_cycle THEN 'CYCLE DETECTED' ELSE 'OK' END AS status
FROM graph_traversal;

-- Shortest path in graph
WITH RECURSIVE shortest_path AS (
  SELECT
    from_node,
    to_node,
    ARRAY[from_node, to_node] AS path,
    1 AS distance
  FROM graph
  WHERE from_node = 1

  UNION ALL

  SELECT
    g.from_node,
    g.to_node,
    sp.path || g.to_node,
    sp.distance + 1
  FROM graph g
  JOIN shortest_path sp ON g.from_node = sp.to_node
  WHERE NOT (g.to_node = ANY(sp.path))
    AND sp.distance < 10
)
SELECT DISTINCT ON (to_node)
  to_node,
  path,
  distance
FROM shortest_path
WHERE to_node = 4  -- Target node
ORDER BY to_node, distance
LIMIT 1;

-- Bill of Materials (BOM)
CREATE TABLE parts (
  part_id INT PRIMARY KEY,
  part_name VARCHAR(100),
  unit_cost DECIMAL(10,2)
);

CREATE TABLE assembly (
  parent_part_id INT REFERENCES parts(part_id),
  child_part_id INT REFERENCES parts(part_id),
  quantity INT,
  PRIMARY KEY (parent_part_id, child_part_id)
);

INSERT INTO parts VALUES
(1, 'Bicycle', 0),
(2, 'Frame', 100),
(3, 'Wheel', 50),
(4, 'Tire', 15),
(5, 'Rim', 25),
(6, 'Spoke', 1);

INSERT INTO assembly VALUES
(1, 2, 1),  -- Bicycle has 1 Frame
(1, 3, 2),  -- Bicycle has 2 Wheels
(3, 4, 1),  -- Wheel has 1 Tire
(3, 5, 1),  -- Wheel has 1 Rim
(5, 6, 36); -- Rim has 36 Spokes

-- Exploded BOM (all parts needed)
WITH RECURSIVE bom AS (
  -- Base case: Top-level product
  SELECT
    child_part_id AS part_id,
    quantity,
    1 AS level
  FROM assembly
  WHERE parent_part_id = 1  -- Bicycle

  UNION ALL

  -- Recursive case: Sub-assemblies
  SELECT
    a.child_part_id,
    b.quantity * a.quantity,
    b.level + 1
  FROM assembly a
  JOIN bom b ON a.parent_part_id = b.part_id
)
SELECT
  p.part_name,
  SUM(b.quantity) AS total_quantity,
  p.unit_cost,
  SUM(b.quantity * p.unit_cost) AS total_cost,
  MAX(b.level) AS max_depth
FROM bom b
JOIN parts p ON b.part_id = p.part_id
GROUP BY p.part_name, p.unit_cost
ORDER BY max_depth, p.part_name;

-- Fibonacci sequence (non-hierarchical recursion)
WITH RECURSIVE fibonacci(n, fib, next_fib) AS (
  SELECT 1, 0::BIGINT, 1::BIGINT

  UNION ALL

  SELECT n + 1, next_fib, fib + next_fib
  FROM fibonacci
  WHERE n < 20
)
SELECT n, fib FROM fibonacci;

-- Generate date series
WITH RECURSIVE date_series AS (
  SELECT DATE '2024-01-01' AS date

  UNION ALL

  SELECT date + INTERVAL '1 day'
  FROM date_series
  WHERE date < DATE '2024-01-31'
)
SELECT date, EXTRACT(DOW FROM date) AS day_of_week
FROM date_series;

-- Flatten JSON tree
CREATE TABLE json_tree (
  id SERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO json_tree (data) VALUES
('{
  "name": "root",
  "children": [
    {"name": "child1", "children": [{"name": "grandchild1"}]},
    {"name": "child2"}
  ]
}');

WITH RECURSIVE flatten AS (
  SELECT
    data ->> 'name' AS name,
    data -> 'children' AS children,
    1 AS level
  FROM json_tree

  UNION ALL

  SELECT
    elem ->> 'name',
    elem -> 'children',
    f.level + 1
  FROM flatten f,
       jsonb_array_elements(f.children) elem
  WHERE f.children IS NOT NULL
)
SELECT name, level
FROM flatten;

2 files · sql Explain with highlit

Recursive CTEs traverse hierarchical data—org charts, category trees, graphs. I use WITH RECURSIVE for self-referential queries. Base case provides starting rows. Recursive case joins to previous iteration. Understanding termination prevents infinite loops. Level tracking shows depth in hierarchy. Path tracking prevents cycles. Hierarchical queries replace multiple application queries. Ancestors and descendants queries navigate both directions. Tree aggregations sum across hierarchies. Proper recursive queries efficiently handle arbitrary-depth structures. Essential for organizational hierarchies, bill of materials, social networks, file systems. PostgreSQL recursive CTEs are powerful for graph traversal.