Common table expressions
WITH for readable steps, inlined or materialized CTEs, recursive queries and data changes in one statement.
Draft. This page is new and has not yet been reviewed by a second person. Every query output on it was produced by a real run on PostgreSQL 18.6 (PGlite 0.5.9). If something looks wrong, report it.
On this page
A common table expression (CTE) is a named subquery written up front with WITH. It makes long queries readable as a sequence of steps, it is the only way to write recursive queries in SQL, and it can chain data changes together. Since PostgreSQL 12, it usually costs nothing extra.
Readable steps#
Big spenders in 2024: first total each user's orders, then join the totals to users.
with spend as (
select user_id, sum(total) as spent, count(*) as orders
from orders
where created_at >= '2024-01-01'
group by user_id
)
select u.email, s.orders, s.spent
from spend s
join users u on u.id = s.user_id
order by s.spent desc
limit 5;| orders | spent | |
|---|---|---|
| user1@example.com | 345 | 209785.27 |
| user2@example.com | 131 | 83559.48 |
| user3@example.com | 116 | 63207.10 |
| user4@example.com | 86 | 56417.82 |
| user5@example.com | 81 | 52498.57 |
Inlined or materialized#
A CTE that is referenced once and has no side effects is inlined: the planner treats it as if you had written a subquery, so conditions from the outer query can be pushed into it and indexes can be used.
create index orders_created_at_idx on orders (created_at);with recent as (
select * from orders where created_at >= '2024-12-01'
)
select count(*) from recent where status = 'pending';Aggregate (cost=164.71..164.72 rows=1 width=8)
-> Index Scan using orders_created_at_idx on orders (cost=0.29..164.31 rows=160 width=0)
Index Cond: (created_at >= '2024-12-01 00:00:00+00'::timestamp with time zone)
Filter: (status = 'pending'::text)Mark it MATERIALIZED and it is computed once, on its own, into a temporary result; the outer query reads that result with a CTE Scan, and its conditions can no longer reach inside:
with recent as materialized (
select * from orders where created_at >= '2024-12-01'
)
select count(*) from recent where status = 'pending';Aggregate (cost=244.73..244.74 rows=1 width=8)
CTE recent
-> Index Scan using orders_created_at_idx on orders (cost=0.29..154.31 rows=4001 width=31)
Index Cond: (created_at >= '2024-12-01 00:00:00+00'::timestamp with time zone)
-> CTE Scan on recent (cost=0.00..90.02 rows=160 width=0)
Filter: (status = 'pending'::text)Materializing is the right call when a CTE is expensive and referenced several times, or when you deliberately want to stop the planner from merging it into the outer query. Before PostgreSQL 12 every CTE was materialized, which is why older advice warns that CTEs are an "optimisation fence".
Recursive queries#
A recursive CTE has a starting query, UNION ALL, and a query that refers to the CTE itself. It repeats the second part until it produces no new rows, which is how trees and graphs are walked in SQL. A small organisation chart:
create table staff (id int primary key, name text not null, manager_id int references staff);
insert into staff values (1, 'Mira', null), (2, 'Theo', 1), (3, 'Lena', 1), (4, 'Omar', 2), (5, 'Ines', 2), (6, 'Kai', 3), (7, 'Zoe', 4);with recursive chain as (
select id, name, 0 as depth, name::text as path
from staff
where manager_id is null
union all
select s.id, s.name, c.depth + 1, c.path || ' > ' || s.name
from staff s
join chain c on s.manager_id = c.id
)
select depth, path from chain order by path;| depth | path |
|---|---|
| 0 | Mira |
| 1 | Mira > Lena |
| 2 | Mira > Lena > Kai |
| 1 | Mira > Theo |
| 2 | Mira > Theo > Ines |
| 2 | Mira > Theo > Omar |
| 3 | Mira > Theo > Omar > Zoe |
Two safety rules. Make sure the recursion stops: a cycle in the data (someone managing their own manager) would loop forever, which the CYCLE clause (PostgreSQL 14 and later) or a depth limit prevents. And index the column the recursive step joins on (here manager_id), because the recursive part runs once per level.
Data changes in a CTE#
A CTE can contain INSERT, UPDATE or DELETE with RETURNING, and the rest of the query can use the rows it returns. Moving the first week of events to an archive in one statement:
create table events_archive (like events);with moved as (
delete from events where at < '2024-01-08' returning *
), archived as (
insert into events_archive select * from moved returning 1
)
select count(*) as moved_to_archive from archived;| moved_to_archive |
|---|
| 1151 |
The whole statement runs as one unit: either every row is moved, or nothing changes.
In MySQL#
Not run here; from the MySQL reference manual. MySQL 8.0 added CTEs, including recursive ones, and the optimizer can merge a CTE into the outer query or materialize it. CTEs in MySQL can only contain SELECT; data-changing CTEs as above are a PostgreSQL feature.
Sources#
- PostgreSQL documentation: WITH Queries (Common Table Expressions), including Recursive Queries, CTE Materialization, Search Order and Cycle Detection and Data-Modifying Statements in WITH; PostgreSQL 12 release notes (CTE inlining).
- MySQL reference manual: WITH (Common Table Expressions).