PostgreSQL recursive CTE for tree/graph queries

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

Use recursive CTEs to traverse trees:

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

-- Get full path from root to leaf:
WITH RECURSIVE tree AS (
-- Anchor: start at the target node
SELECT id, name, parent_id, name::text AS path, 0 AS depth
FROM categories
WHERE id = 42

UNION ALL

-- Recursive: walk up to root
SELECT c.id, c.name, c.parent_id,
c.name || ' > ' || t.path,
t.depth + 1
FROM categories c
JOIN tree t ON c.id = t.parent_id
)
SELECT * FROM tree ORDER BY depth DESC LIMIT 1;
-- Result: 'Electronics > Phones > Smartphones'

-- Get all descendants of a node:
WITH RECURSIVE descendants AS (
SELECT id, name, parent_id, 0 AS depth
FROM categories WHERE id = 1 -- Root

UNION ALL

SELECT c.id, c.name, c.parent_id, d.depth + 1
FROM categories c
JOIN descendants d ON c.parent_id = d.id
)
SELECT * FROM descendants;

-- With depth limit (prevent infinite loops):
WITH RECURSIVE tree AS (
SELECT id, parent_id, 0 AS depth FROM categories WHERE id = 1
UNION ALL
SELECT c.id, c.parent_id, t.depth + 1
FROM categories c JOIN tree t ON c.parent_id = t.id
WHERE t.depth < 10 -- Safety limit
)
SELECT * FROM tree;

-- Alternative: ltree extension for path-based trees
-- CREATE EXTENSION ltree;
-- WHERE path <@ 'root.electronics.phones'

Recursive CTEs handle arbitrary-depth trees in a single query, avoiding the N+1 problem of fetching children level by level.