WITH (CTE)
Named query step declared up-front with WITH. Turns a nested subquery into a reusable, top-down read: each CTE is a labeled building block, referenced by later CTEs or the final SELECT. Scope is the containing statement only. CTEs aren't views; they disappear after the query runs.
Syntax #
sql
WITH step_1 AS (
SELECT ...
),
step_2 AS (
SELECT ...
FROM step_1
)
SELECT *
FROM step_2;Example #
Loading SQL editor...
Info
CTEs are scoped to a single statement, not the session. If you need the same intermediate result across multiple queries, use a VIEW (logical, re-runs each query) or a TEMPORARY TABLE (materialized, persists for the session). Also: historical Postgres (≤ 11) always materialized CTEs, acting as an optimizer fence. Postgres 12+ inlines simple CTEs by default, so add WITH x AS MATERIALIZED (...) to force the old behavior when the planner is making bad choices.