Common Table Expressions (CTEs)

A CTE names a subquery with WITH so the main query can use it like a table. The examples use an org chart, which is also what recursive CTEs are best at.

-- setup.sql
CREATE TABLE staff (
    id         INT PRIMARY KEY,
    name       TEXT NOT NULL,
    manager_id INT REFERENCES staff(id),
    salary     INT NOT NULL
);
INSERT INTO staff VALUES
    (1, 'Ana', NULL, 150000),
    (2, 'Ben', 1,    110000), (3, 'Cai', 1, 105000),
    (4, 'Dee', 2,     80000), (5, 'Eli', 2,  78000),
    (6, 'Fay', 3,     70000), (7, 'Gus', 6,  55000);

$ psql -d sqldemo -f setup.sql
CREATE TABLE
INSERT 0 7

A Basic WITH Clause

The CTE computes each manager’s team size; the main query joins it back for names.

-- basic.sql
WITH team_sizes AS (
    SELECT manager_id, count(*) AS reports
    FROM staff
    WHERE manager_id IS NOT NULL
    GROUP BY manager_id
)
SELECT s.name, t.reports
FROM team_sizes t
JOIN staff s ON s.id = t.manager_id
ORDER BY t.reports DESC, s.name;

$ psql -d sqldemo -f basic.sql
 name | reports 
------+---------
 Ana  |       2
 Ben  |       2
 Cai  |       1
 Fay  |       1
(4 rows)

team_sizes exists only for this one statement. The result is the same as putting the subquery inline in FROM, but the step now has a name that says what it holds.

Chaining CTEs

Later CTEs can read earlier ones, which turns a nested query into a list of steps.

-- chained.sql
WITH payroll AS (
    SELECT sum(salary) AS total FROM staff
),
shares AS (
    SELECT name, salary, round(100.0 * salary / payroll.total, 1) AS pct
    FROM staff, payroll
)
SELECT name, salary, pct
FROM shares
WHERE pct >= 15
ORDER BY pct DESC;

$ psql -d sqldemo -f chained.sql
 name | salary | pct  
------+--------+------
 Ana  | 150000 | 23.1
 Ben  | 110000 | 17.0
 Cai  | 105000 | 16.2
(3 rows)

shares reads payroll as if it were a one-row table. Using 100.0 instead of 100 avoids integer division, which would have rounded every share down to a whole number before round ever saw it.

MATERIALIZED vs NOT MATERIALIZED

Since PostgreSQL 12, a CTE used once is folded into the main query. MATERIALIZED forces it to be computed separately first; the plans show the difference.

-- materialized.sql
EXPLAIN (COSTS OFF)
WITH s AS (SELECT * FROM staff)
SELECT name FROM s WHERE id = 4;

EXPLAIN (COSTS OFF)
WITH s AS MATERIALIZED (SELECT * FROM staff)
SELECT name FROM s WHERE id = 4;

$ psql -d sqldemo -f materialized.sql
              QUERY PLAN              
--------------------------------------
 Index Scan using staff_pkey on staff
   Index Cond: (id = 4)
(2 rows)

        QUERY PLAN         
---------------------------
 CTE Scan on s
   Filter: (id = 4)
   CTE s
     ->  Seq Scan on staff
(4 rows)

In the first plan the CTE has disappeared: the id = 4 condition went straight into an index scan on staff. In the second, PostgreSQL builds the whole CTE (CTE s, a scan of every row) and only then filters it. Materializing is worth it when a CTE is expensive and read several times; otherwise let the planner inline it.

Recursive CTEs

WITH RECURSIVE has an anchor (where to start) and a recursive step (how to go one level further). This walks the whole chart from the top.

-- recursive.sql
WITH RECURSIVE chart AS (
    SELECT id, name, 0 AS depth, name AS path
    FROM staff WHERE manager_id IS NULL                -- anchor: the root
    UNION ALL
    SELECT s.id, s.name, c.depth + 1, c.path || ' > ' || s.name
    FROM staff s JOIN chart c ON s.manager_id = c.id   -- one level down
)
SELECT repeat('  ', depth) || name AS tree, path
FROM chart
ORDER BY path;

$ psql -d sqldemo -f recursive.sql
   tree    |         path          
-----------+-----------------------
 Ana       | Ana
   Ben     | Ana > Ben
     Dee   | Ana > Ben > Dee
     Eli   | Ana > Ben > Eli
   Cai     | Ana > Cai
     Fay   | Ana > Cai > Fay
       Gus | Ana > Cai > Fay > Gus
(7 rows)

The anchor produced Ana. Each pass then found the staff whose manager was in the previous pass: Ben and Cai, then Dee, Eli and Fay, then Gus. The pass after Gus found nobody, so the recursion stopped. Sorting by the accumulated path puts each person under their manager.

When the Data Has a Cycle

Recursion stops only when a pass finds no new rows. Make Ana report to Gus, who is at the bottom of her own chain, and walking up from Gus never ends. A statement timeout stops it.

-- cycle_hang.sql
UPDATE staff SET manager_id = 7 WHERE id = 1;
SET statement_timeout = '2s';
WITH RECURSIVE up AS (
    SELECT id, manager_id FROM staff WHERE id = 7
    UNION ALL
    SELECT s.id, s.manager_id FROM staff s JOIN up ON s.id = up.manager_id
)
SELECT count(*) FROM up;

$ psql -d sqldemo -f cycle_hang.sql
UPDATE 1
SET
psql:cycle_hang.sql:9: ERROR:  canceling statement due to statement timeout

UNION ALL happily revisits the same people forever: Gus, Fay, Cai, Ana, Gus again. Without the timeout this query would run until it exhausted memory or disk. PostgreSQL 14 added a CYCLE clause that detects the loop.

-- cycle_fixed.sql
WITH RECURSIVE up AS (
    SELECT id, manager_id FROM staff WHERE id = 7
    UNION ALL
    SELECT s.id, s.manager_id FROM staff s JOIN up ON s.id = up.manager_id
) CYCLE id SET is_cycle USING seen
SELECT id, manager_id, is_cycle FROM up;

$ psql -d sqldemo -f cycle_fixed.sql
 id | manager_id | is_cycle 
----+------------+----------
  7 |          6 | f
  6 |          3 | f
  3 |          1 | f
  1 |          7 | f
  7 |          6 | t
(5 rows)

CYCLE id keeps the list of visited ids in seen. When Gus (id 7) comes around a second time, that row is marked is_cycle = t and the recursion does not continue from it. The chain is walked exactly once.

CTEs vs Subqueries

Reach for a CTE when a query has steps worth naming, when the same derived result is used twice, or when you need recursion. For a single small derived table, an inline subquery is just as clear.

Practice

1. Undo the cycle, then list everyone under Ben (not including Ben) with a recursive CTE.

-- practice_under_ben.sql
UPDATE staff SET manager_id = NULL WHERE id = 1;
WITH RECURSIVE team AS (
    SELECT id, name FROM staff WHERE manager_id = 2
    UNION ALL
    SELECT s.id, s.name FROM staff s JOIN team t ON s.manager_id = t.id
)
SELECT name FROM team ORDER BY name;

$ psql -d sqldemo -f practice_under_ben.sql
UPDATE 1
 name 
------
 Dee
 Eli
(2 rows)

2. Using a recursive CTE, compute each person’s depth and report the deepest level.

-- practice_depth.sql
WITH RECURSIVE chart AS (
    SELECT id, 0 AS depth FROM staff WHERE manager_id IS NULL
    UNION ALL
    SELECT s.id, c.depth + 1 FROM staff s JOIN chart c ON s.manager_id = c.id
)
SELECT max(depth) AS deepest_level FROM chart;

$ psql -d sqldemo -f practice_depth.sql
 deepest_level 
---------------
             3
(1 row)

3. Use a recursive CTE with no table at all to generate the numbers 1 to 5.

-- practice_numbers.sql
WITH RECURSIVE n AS (
    SELECT 1 AS i
    UNION ALL
    SELECT i + 1 FROM n WHERE i < 5
)
SELECT i FROM n;

$ psql -d sqldemo -f practice_numbers.sql
 i 
---
 1
 2
 3
 4
 5
(5 rows)