Common Table Expressions (CTE) in PostgreSQL

A Common Table Expression (CTE) is a named temporary result set defined with the WITH keyword before the main query. CTEs let you split a complex query into readable, named steps that flow top-to-bottom like a pipeline. Unlike a subquery, a CTE can be referenced multiple times in the same query, and it can contain INSERT, UPDATE, or DELETE statements for atomic multi-step data operations.

Syntax

WITH cte_name AS (
  SELECT ...
)
SELECT * FROM cte_name;

Multiple CTEs are separated by commas. Each CTE can reference CTEs defined before it:

WITH
  cte_one AS (
    SELECT ...
  ),
  cte_two AS (
    SELECT ... FROM cte_one
  )
SELECT * FROM cte_two;

Practical Example

Build a monthly revenue report with month-over-month growth in two named steps:

CREATE TABLE orders (
  order_id     SERIAL PRIMARY KEY,
  placed_at    TIMESTAMPTZ NOT NULL,
  total_amount NUMERIC(10, 2) NOT NULL
);

INSERT INTO orders (placed_at, total_amount) VALUES
  ('2025-01-05', 220.00), ('2025-01-18', 310.00),
  ('2025-02-02', 190.00), ('2025-02-14', 450.00), ('2025-02-28', 80.00),
  ('2025-03-07', 330.00), ('2025-03-21', 270.00);

Step 1 — aggregate monthly revenue. Step 2 — compute month-over-month change:

WITH
monthly_revenue AS (
  SELECT
    date_trunc('month', placed_at) AS month,
    COUNT(*)                        AS order_count,
    SUM(total_amount)               AS revenue
  FROM orders
  GROUP BY 1
),
revenue_with_growth AS (
  SELECT
    month,
    order_count,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
      (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100,
      1
    ) AS growth_pct
  FROM monthly_revenue
)
SELECT *
FROM revenue_with_growth
ORDER BY month;

Result:

        month        | order_count | revenue  | prev_month_revenue | growth_pct
---------------------+-------------+----------+--------------------+------------
 2025-01-01 00:00:00 |           2 |   530.00 |               NULL |       NULL
 2025-02-01 00:00:00 |           3 |   720.00 |             530.00 |       35.8
 2025-03-01 00:00:00 |           2 |   600.00 |             720.00 |      -16.7

Each CTE is a self-contained, testable query. You can debug monthly_revenue independently before adding revenue_with_growth.

Writeable CTE: atomic archive-and-delete

CTEs can contain INSERT, UPDATE, or DELETE with RETURNING, enabling multi-step data changes in a single transaction:

WITH archived AS (
  DELETE FROM orders
  WHERE placed_at < now() - interval '1 year'
  RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM archived;

The DELETE and INSERT are both part of the same transaction. If either step fails, neither is committed.

MATERIALIZED vs inlined CTEs (PostgreSQL 12+)

-- Default in PG 12+: CTE is inlined, planner can optimize it like a subquery
WITH recent_orders AS (
  SELECT * FROM orders WHERE placed_at >= now() - interval '30 days'
)
SELECT * FROM recent_orders WHERE total_amount > 100;

-- Force materialization: CTE is computed once and cached
WITH recent_orders AS MATERIALIZED (
  SELECT * FROM orders WHERE placed_at >= now() - interval '30 days'
)
SELECT * FROM recent_orders WHERE total_amount > 100;

Use MATERIALIZED when you want the CTE result fixed — for example, to prevent the planner from re-evaluating the CTE differently each time it is referenced, or when the CTE contains volatile functions.

CTE vs Subquery vs Temporary Table

Use a CTE for readability and reuse within a single query. Use a temporary table when the intermediate result is large, needs an index, or must be referenced across multiple separate queries.

Testing with Vela

CTEs that contain DML (INSERT, UPDATE, DELETE) are especially worth testing before running in production. Vela’s database branching lets you run writeable CTEs on a production-data clone in a sandboxed branch. You can verify the RETURNING output, confirm the row counts match expectations, and roll back the branch rather than the transaction if something is wrong — without any impact on the live database.

Production Tips

• Use CTEs to make long queries reviewable. Each CTE is a named, independently testable step — colleagues and future-you can read them top-to-bottom.
• In PostgreSQL 12+, CTEs are inlined by default and carry no performance overhead over equivalent subqueries. Older versions always materialized CTEs, which could hurt or help performance depending on the query.
• Add MATERIALIZED when you explicitly want the result computed once — useful when a CTE is referenced multiple times and the planner would otherwise evaluate it independently each time.
• For recursive queries (hierarchy traversal, graph walks), use WITH RECURSIVE. See the Recursive CTE tutorial for the full pattern.
• Writeable CTEs are atomic: all DML inside the WITH clause and the outer query execute in a single transaction snapshot.