Common Table Expressions (CTEs) in PostgreSQL

A Common Table Expression, written with a WITH clause, lets you name a subquery and reference it as if it were a temporary table for the rest of the statement. CTEs exist primarily to make complex SQL readable: instead of nesting subqueries three levels deep, you can break a query into named, sequential steps that read almost like a small program.

Beyond readability, PostgreSQL also supports recursive CTEs, which can express queries that would otherwise be impossible in plain SQL — such as walking a tree or graph structure of arbitrary depth.

Why it matters

A query with subqueries nested four levels deep is correct and unreadable. A common table expression names each step and lets the query read from top to bottom. The recursive form does something plain SQL otherwise cannot: walk a hierarchy of unknown depth, such as a reporting chain or a category tree.

Basic WITH Clause Syntax

WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name; You can define multiple CTEs in one WITH clause, separated by commas, and later CTEs can reference earlier ones. This turns a deeply nested query into a readable sequence of clearly named steps, each of which can also be tested independently by simply running just that subquery.

Recursive CTEs

A recursive CTE uses WITH RECURSIVE name AS (base_case UNION ALL recursive_case). The base case produces the starting rows; the recursive case refers back to the CTE itself and keeps running, accumulating results, until it produces no more new rows. This is the standard way to query hierarchical data, such as an employee-to-manager reporting chain or a category tree with arbitrary nesting depth.

CTEs vs. Subqueries: Readability, Not Always Performance

In modern PostgreSQL (12 and later), a non-recursive CTE is generally inlined into the main query by the planner just like a subquery would be, unless it is referenced multiple times, is recursive, or you explicitly force materialization with MATERIALIZED. So the main reason to reach for a CTE today is clarity of expression, not a guaranteed performance difference — though MATERIALIZED is available when you specifically want the CTE computed once and reused.

Examples

WITH expensive_products AS (
    SELECT id, name, price
    FROM products
    WHERE price > 500
),
by_category AS (
    SELECT category_id, COUNT(*) AS expensive_count
    FROM products
    JOIN expensive_products USING (id)
    GROUP BY category_id
)
SELECT c.name, b.expensive_count
FROM by_category b
JOIN categories c ON c.id = b.category_id;

   name   | expensive_count
----------+-----------------
 Laptops  |               1

WITH RECURSIVE reports_to (id, name, manager_id, depth) AS (
    SELECT id, name, manager_id, 0
    FROM employees
    WHERE manager_id IS NULL          -- base case: the top of the chain
    UNION ALL
    SELECT e.id, e.name, e.manager_id, r.depth + 1
    FROM employees e
    JOIN reports_to r ON e.manager_id = r.id
)
SELECT * FROM reports_to ORDER BY depth;

 id |   name   | manager_id | depth
----+----------+------------+-------
  1 | Priya    |            |     0
  2 | Karan    |          1 |     1
  3 | Sanya    |          2 |     2

Exercise

Each employee has a manager_id. Using a recursive CTE, list Anil and then everyone above him in the reporting chain, from Anil up to the top.

Expected output: name, Anil, Asha, Zoya (one per line)

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, manager_id INTEGER);
INSERT INTO employees (id, name, manager_id) VALUES
  (1, 'Zoya', NULL),
  (2, 'Asha', 1),
  (3, 'Ravi', 1),
  (4, 'Anil', 2);

-- TODO: a recursive CTE that starts at Anil and follows manager_id upwards

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, manager_id INTEGER);
INSERT INTO employees (id, name, manager_id) VALUES
  (1, 'Zoya', NULL),
  (2, 'Asha', 1),
  (3, 'Ravi', 1),
  (4, 'Anil', 2);

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE name = 'Anil'

  UNION ALL

  SELECT e.id, e.name, e.manager_id, chain.level + 1
  FROM employees e
  JOIN chain ON e.id = chain.manager_id
)
SELECT name
FROM chain
ORDER BY level;

Quick quiz

Interview questions