CTEs and Recursive Queries

Anchor plus recursive member

A CTE (WITH name AS (...)) names an intermediate result, making long queries readable step by step; PostgreSQL inlines simple CTEs into the main query, so they are usually not a performance barrier. A recursive CTE has an anchor query (the roots) and a recursive part joined to the previous results, repeated until no new rows appear. It walks org charts, category trees and bill-of-materials. Track depth and a path, and guard against cycles in graph data.

Compose queries from parts

CTEs name steps, recursive CTEs walk hierarchies, LATERAL runs a subquery per row, and set operations combine results.

Walking the management chain, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). Asha is the root; each level adds one to depth and extends the path string.

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 0 AS depth, name::text AS path
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1, c.path || ' > ' || e.name
  FROM employees e JOIN chain c ON e.manager_id = c.id
)
SELECT depth, path FROM chain ORDER BY path;

Output:

 depth |        path        
-------+--------------------
     0 | Asha
     1 | Asha > Meera
     1 | Asha > Nina
     1 | Asha > Omar
     1 | Asha > Ravi
     2 | Asha > Ravi > John
     1 | Asha > Sara
     2 | Asha > Sara > Ken
     2 | Asha > Sara > Li
(9 rows)

Add a depth limit for untrusted data

A WHERE depth < 20 condition stops runaway recursion if the data contains a cycle.

Quick check: What are the two parts of a recursive CTE?

• A SELECT and an ORDER BY
• An anchor query and a recursive query joined to the previous results
• Two tables of equal size
• A trigger and a function