CTEs & Recursive Queries

Basic CTE

WITH dept_stats AS (
  SELECT department, count(*) AS total, avg(cgpa) AS avg_cgpa
  FROM students
  GROUP BY department
)
SELECT * FROM dept_stats WHERE avg_cgpa > 3.0 ORDER BY total DESC;

Multiple CTEs

WITH
active_depts AS (
  SELECT code, name_en FROM departments WHERE active
),
dept_stats AS (
  SELECT s.department, count(*) AS total, avg(s.cgpa) AS avg_cgpa
  FROM students s
  JOIN active_depts a ON s.department = a.code
  GROUP BY s.department
)
SELECT a.name_en, d.total, round(d.avg_cgpa, 2) AS avg_cgpa
FROM dept_stats d
JOIN active_depts a ON a.code = d.department;

MATERIALIZED and NOT MATERIALIZED

Before version 12, a CTE was always an optimisation fence — it was executed separately and its results could not be pushed into. Since 12, Postgres inlines a CTE that is used once, unless you say otherwise:

WITH heavy AS MATERIALIZED (SELECT … )        -- force it to run once, separately
WITH light AS NOT MATERIALIZED (SELECT … )    -- force inlining

Use MATERIALIZED when the CTE is expensive and referenced several times; use NOT MATERIALIZED when you want the planner to push a WHERE condition down into it.

Recursive CTEs

Two parts joined by UNION ALL: an anchor that seeds the result, and a recursive member that refers back to the CTE.

WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

Traversing a hierarchy

WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 1 AS level, name::text AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, oc.level + 1, oc.path || ' > ' || e.name
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT level, path FROM org_chart ORDER BY path;

The same pattern walks category trees, threaded comments, bill-of-materials structures and graph paths.

To guard against cycles in messy data, track visited ids — or use the CYCLE clause available since Postgres 14:

WITH RECURSIVE t AS (
  SELECT id, parent_id FROM nodes WHERE id = 1
  UNION ALL
  SELECT n.id, n.parent_id FROM nodes n JOIN t ON n.parent_id = t.id
) CYCLE id SET is_cycle USING path
SELECT * FROM t WHERE NOT is_cycle;

Data-modifying CTEs

This is a genuinely distinctive Postgres feature: INSERT, UPDATE and DELETE can appear inside a WITH clause, with RETURNING feeding the next step. It lets you move rows between tables atomically in one statement:

WITH moved AS (
  DELETE FROM students
  WHERE status = 'graduated'
  RETURNING id, name_en, department
)
INSERT INTO alumni (student_id, name_en, department)
SELECT id, name_en, department FROM moved;

WITH updated AS (
  UPDATE students SET cgpa = cgpa + 0.1 WHERE department = 'CSE'
  RETURNING id, cgpa
)
SELECT count(*), avg(cgpa) FROM updated;

All sub-statements in one WITH see the same snapshot of the data, and they all commit or roll back together.

Recursion limits

There is no fixed recursion cap; a runaway recursive CTE will run until it exhausts memory or you cancel it. Always include a terminating condition, and consider LIMIT while developing.