Skip to content
All glossary terms

Common table expression (CTE)

What is a CTE (WITH query) in PostgreSQL?

A common table expression, written with WITH, gives a subquery a name so the rest of the statement can use it like a table. WITH RECURSIVE handles trees and hierarchies.

A common table expression (CTE) is an auxiliary statement in a WITH clause that defines a temporary, named result for a single query. It breaks a complicated query into readable steps, and the auxiliary statement can also be a data-modifying INSERT, UPDATE or DELETE.

WITH RECURSIVE lets a CTE refer to its own output, which is the standard way to walk hierarchies such as reporting lines or nested categories. A non-recursive, side-effect-free CTE that the query references only once is folded into the outer query so both can be optimized together. Add MATERIALIZED to have it evaluated separately, or NOT MATERIALIZED to ask for folding.

Example

The example starts from one employee and climbs the management chain with WITH RECURSIVE, returning each person with their distance from the start.

CREATE TABLE employees (
  id int PRIMARY KEY,
  name text NOT NULL,
  manager_id int REFERENCES employees (id)
);
INSERT INTO employees VALUES (1, 'Ana', NULL), (2, 'Ben', 1), (3, 'Chen', 2), (4, 'Dee', 1);

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS depth FROM employees WHERE id = 3
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM employees e JOIN chain c ON e.id = c.manager_id
)
SELECT name, depth FROM chain ORDER BY depth;

Plain SQL for stock PostgreSQL 16 or 17, no extensions needed. Run it in an empty database you can throw away.

Sources

Official PostgreSQL documentation (in English).

Related terms