PostgreSQL recursive CTEs for hierarchical data

Need to query hierarchical data (org charts, categories, threaded comments) stored in a self-referencing table.

Use recursive CTEs to traverse hierarchies:

-- Table: categories (id, name, parent_id)

-- Get full path from root to each category
WITH RECURSIVE category_tree AS (
  -- Base case: root categories
  SELECT id, name, parent_id, name::text AS path, 0 AS depth
  FROM categories
  WHERE parent_id IS NULL
  
  UNION ALL
  
  -- Recursive case: children
  SELECT c.id, c.name, c.parent_id,
         ct.path || ' > ' || c.name,
         ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY path;

-- Result:
-- Electronics
-- Electronics > Computers
-- Electronics > Computers > Laptops
-- Electronics > Phones

-- Get all descendants of a specific node
WITH RECURSIVE descendants AS (
  SELECT id, name, parent_id FROM categories WHERE id = 5
  UNION ALL
  SELECT c.id, c.name, c.parent_id
  FROM categories c JOIN descendants d ON c.parent_id = d.id
)
SELECT * FROM descendants;

-- Threaded comments with indentation
WITH RECURSIVE thread AS (
  SELECT id, content, parent_id, author,
         ARRAY[id] AS path, 0 AS depth
  FROM comments WHERE parent_id IS NULL AND post_id = 42
  
  UNION ALL
  
  SELECT c.id, c.content, c.parent_id, c.author,
         t.path || c.id, t.depth + 1
  FROM comments c JOIN thread t ON c.parent_id = t.id
)
SELECT repeat('  ', depth) || author || ': ' || content
FROM thread ORDER BY path;

-- IMPORTANT: Always add a depth limit to prevent infinite loops
WITH RECURSIVE tree AS (
  SELECT id, parent_id, 0 AS depth FROM nodes WHERE id = 1
  UNION ALL
  SELECT n.id, n.parent_id, t.depth + 1
  FROM nodes n JOIN tree t ON n.parent_id = t.id
  WHERE t.depth < 100  -- Safety limit!
)
SELECT * FROM tree;

Recursive CTEs let SQL traverse graph/tree structures that would otherwise require multiple queries or application-level recursion.

Querying hierarchical or tree-structured data in PostgreSQL