CTEs and Recursive Queries

An org chart traversal loops through 7 levels in application code, one database round trip per level. Replace it with one recursive query.

A CTE — Common Table Expression, written with WITH — gives a subquery a name and lets it be referenced like a table for the rest of the statement. Used non-recursively, this is mostly about readability: breaking a complex query into named, independently-checkable steps, each of which can be run and inspected on its own while building the final query. Used recursively, with WITH RECURSIVE, a CTE can reference itself, which is exactly what a hierarchy traversal needs: start from a base case (the top of the org chart, or a graph's root nodes), then repeatedly join the CTE's own growing result back against the base table to find the next level down, until no new rows appear.\n\nThat repetition is powerful and genuinely dangerous in the same breath: if the underlying data contains a real circular reference — an employee whose reporting chain eventually loops back to themselves, a common real-world data-quality bug — a recursive CTE with no protection will recurse forever, exactly as a loop in any programming language would with no exit condition. PostgreSQL's CYCLE clause exists specifically to detect that a value has already been visited on the current path and safely stop, rather than depending on a maximum-depth guess or an accidental crash to end it.

A Multi-Step CTE for Readability

WITH mgr_counts AS (
  SELECT manager_id, count(*) AS direct_reports
  FROM sql_employees WHERE manager_id IS NOT NULL GROUP BY manager_id
),
named AS (
  SELECT e.name, mc.direct_reports FROM mgr_counts mc JOIN sql_employees e ON e.id = mc.manager_id
)
SELECT * FROM named ORDER BY direct_reports DESC;

     name     | direct_reports
--------------+----------------
 CEO          |              2
 VP Eng       |              2
 Director Eng |              1
 Eng Manager  |              1

Each CTE step is independently runnable and checkable on its own — mgr_counts alone can be verified before ever building named on top of it.

A Recursive CTE, Traversing a Real Hierarchy

WITH RECURSIVE reporting_path AS (
  SELECT id, name, manager_id, name::text AS path
  FROM sql_employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, rp.path || ' -> ' || e.name
  FROM sql_employees e JOIN reporting_path rp ON e.manager_id = rp.id
)
SELECT id, path FROM reporting_path ORDER BY id;

 id |                        path
----+-----------------------------------------------------
  1 | CEO
  2 | CEO -> VP Eng
  4 | CEO -> VP Eng -> Director Eng
  7 | CEO -> VP Eng -> Director Eng -> Eng Manager -> You

One query, one round trip, all 7 levels — the base case matches the root (manager_id IS NULL), and the recursive term joins the CTE's own growing output back to the table to walk one level deeper each iteration, stopping automatically once a level produces no new matching rows.

A Real Circular Reference, Genuinely Dangerous

UPDATE sql_employees SET manager_id = 7 WHERE id = 4;  -- Director Eng now reports to You

Director Eng (id 4) is several levels above You (id 7) in the original chain — reassigning their manager to You creates a genuine loop: You → Eng Manager → Director Eng → You → ... With no protection and no LIMIT, the identical recursive query above would never terminate, exactly as a while loop with no exit condition would not.

CYCLE, Detecting and Halting It For Real

WITH RECURSIVE reporting_path AS (
  SELECT id, name, manager_id, name::text AS path FROM sql_employees WHERE id = 7
  UNION ALL
  SELECT e.id, e.name, e.manager_id, rp.path || ' -> ' || e.name
  FROM sql_employees e JOIN reporting_path rp ON e.id = rp.manager_id
)
CYCLE id SET is_cycle USING path_arr
SELECT id, path, is_cycle FROM reporting_path;

 id |                   path                    | is_cycle
----+--------------------------------------------+----------
  7 | You                                        | f
  6 | You -> Eng Manager                         | f
  4 | You -> Eng Manager -> Director Eng         | f
  7 | You -> Eng Manager -> Director Eng -> You  | t

CYCLE id SET is_cycle USING path_arr tracks every id value already visited on the current path in a hidden array (path_arr), and the moment a row would revisit an id already on that path, is_cycle flips to true and recursion for that branch stops — 4 real rows, not an infinite stream.

Recursive CTE (WITH RECURSIVE)

A CTE that references itself: a base case establishes the starting rows, and a recursive term — joined to the CTE's own name with UNION ALL — produces the next generation of rows from the previous one, repeating until an iteration produces zero new rows. This is the SQL equivalent of a loop, and like any loop, it needs a genuine termination condition; a self-referencing join with no natural stopping point (such as a cyclic hierarchy) will not terminate on its own.

CYCLE clause

A PostgreSQL 14+ clause added directly to a recursive CTE — CYCLE col SET flagcol USING trackcol — that maintains an array of every value of col seen so far on the current recursion path, and sets flagcol to true the moment a row would repeat a value already on that path, stopping further recursion down that branch. It turns an open-ended "trust the data has no cycles" assumption into an enforced, visible guarantee.

📐 Decompose a Query Into Readable CTE Steps

Write a two-step CTE: first count direct reports per manager, then join that back to employee names.

psql -U postgres -d beer_db -c "WITH mgr_counts AS (SELECT manager_id, count(*) AS direct_reports FROM sql_employees WHERE manager_id IS NOT NULL GROUP BY manager_id), named AS (SELECT e.name, mc.direct_reports FROM mgr_counts mc JOIN sql_employees e ON e.id = mc.manager_id) SELECT * FROM named ORDER BY direct_reports DESC;"

student@lab:~$ psql -U postgres -d beer_db -c "WITH mgr_counts AS (SELECT manager_id, count(*) AS direct_reports FROM sql_employees WHERE manager_id IS NOT NULL GROUP BY manager_id), named AS (SELECT e.name, mc.direct_reports FROM mgr_counts mc JOIN sql_employees e ON e.id = mc.manager_id) SELECT * FROM named ORDER BY direct_reports DESC;" SET name | direct_reports --------------+---------------- CEO | 2 VP Eng | 2 Director Eng | 1 Eng Manager | 1 (4 rows)

🌲 Traverse the Real Hierarchy With a Recursive CTE

Write a recursive CTE building the full reporting path for every employee, from the CEO down.

psql -U postgres -d beer_db -c "WITH RECURSIVE reporting_path AS (SELECT id, name, manager_id, name::text AS path FROM sql_employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, rp.path || ' -> ' || e.name FROM sql_employees e JOIN reporting_path rp ON e.manager_id = rp.id) SELECT id, path FROM reporting_path ORDER BY id;"

student@lab:~$ psql -U postgres -d beer_db -c "WITH RECURSIVE reporting_path AS (SELECT id, name, manager_id, name::text AS path FROM sql_employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, rp.path || ' -> ' || e.name FROM sql_employees e JOIN reporting_path rp ON e.manager_id = rp.id) SELECT id, path FROM reporting_path ORDER BY id;" SET id | path ----+----------------------------------------------------- 1 | CEO 2 | CEO -> VP Eng 3 | CEO -> VP Sales 4 | CEO -> VP Eng -> Director Eng 5 | CEO -> VP Eng -> Director Infra 6 | CEO -> VP Eng -> Director Eng -> Eng Manager 7 | CEO -> VP Eng -> Director Eng -> Eng Manager -> You (7 rows)

⚠️ Introduce a Real Circular Reference and See the Danger

Reassign a manager to create a genuine cycle, then run the same kind of recursive query with a LIMIT to safely observe the infinite pattern without actually hanging.

psql -U postgres -d beer_db -c "UPDATE sql_employees SET manager_id = 7 WHERE id = 4;"

psql -U postgres -d beer_db -c "WITH RECURSIVE reporting_path AS (SELECT id, name, manager_id, name::text AS path FROM sql_employees WHERE id = 7 UNION ALL SELECT e.id, e.name, e.manager_id, rp.path || ' -> ' || e.name FROM sql_employees e JOIN reporting_path rp ON e.id = rp.manager_id) SELECT id, path FROM reporting_path LIMIT 8;"

student@lab:~$ psql -U postgres -d beer_db -c "UPDATE sql_employees SET manager_id = 7 WHERE id = 4;" SET UPDATE 1 student@lab:~$ psql -U postgres -d beer_db -c "WITH RECURSIVE reporting_path AS (SELECT id, name, manager_id, name::text AS path FROM sql_employees WHERE id = 7 UNION ALL SELECT e.id, e.name, e.manager_id, rp.path || ' -> ' || e.name FROM sql_employees e JOIN reporting_path rp ON e.id = rp.manager_id) SELECT id, path FROM reporting_path LIMIT 8;" SET id | path ----+-------------------------------------------------------------------------------------------------- 7 | You 6 | You -> Eng Manager 4 | You -> Eng Manager -> Director Eng 7 | You -> Eng Manager -> Director Eng -> You 6 | You -> Eng Manager -> Director Eng -> You -> Eng Manager 4 | You -> Eng Manager -> Director Eng -> You -> Eng Manager -> Director Eng 7 | You -> Eng Manager -> Director Eng -> You -> Eng Manager -> Director Eng -> You 6 | You -> Eng Manager -> Director Eng -> You -> Eng Manager -> Director Eng -> You -> Eng Manager (8 rows)

🛑 Add CYCLE Detection and Confirm It Genuinely Halts

Add the CYCLE clause to the same recursive query and confirm it detects the cycle and stops after exactly 4 rows.

psql -U postgres -d beer_db -c "WITH RECURSIVE reporting_path AS (SELECT id, name, manager_id, name::text AS path FROM sql_employees WHERE id = 7 UNION ALL SELECT e.id, e.name, e.manager_id, rp.path || ' -> ' || e.name FROM sql_employees e JOIN reporting_path rp ON e.id = rp.manager_id) CYCLE id SET is_cycle USING path_arr SELECT id, path, is_cycle FROM reporting_path;"

student@lab:~$ psql -U postgres -d beer_db -c "WITH RECURSIVE reporting_path AS (SELECT id, name, manager_id, name::text AS path FROM sql_employees WHERE id = 7 UNION ALL SELECT e.id, e.name, e.manager_id, rp.path || ' -> ' || e.name FROM sql_employees e JOIN reporting_path rp ON e.id = rp.manager_id) CYCLE id SET is_cycle USING path_arr SELECT id, path, is_cycle FROM reporting_path;" SET id | path | is_cycle ----+--------------------------------------------+---------- 7 | You | f 6 | You -> Eng Manager | f 4 | You -> Eng Manager -> Director Eng | f 7 | You -> Eng Manager -> Director Eng -> You | t (4 rows)

Lab 3.3.1 complete. Recursive CTEs, proven with a genuine hierarchy and a genuine circular reference:\n\n\n Multi-step CTE decomposition : ✅ readable, independently checkable steps\n Recursive traversal, 7 levels : ✅ one query, one round trip\n Real cycle, no protection : ✅ genuine infinite repetition, shown safely\n CYCLE clause, real halt : ✅ exactly 4 rows, is_cycle proven correct\n

Enable JavaScript to run the live terminal and track your progress.