PostgreSQL Common Table Expressions (CTE)

Introduction to PostgreSQL CTEs

A Common Table Expression (CTE) is a named temporary result set defined with the WITH keyword. CTEs let you split a complex query into readable, named steps.

Basic syntax:

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

Simple CTE example

-- Find customers who spent more than $200 last month
WITH high_value_customers AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM payment
  WHERE payment_date >= date_trunc('month', now() - interval '1 month')
    AND payment_date <  date_trunc('month', now())
  GROUP BY customer_id
  HAVING SUM(amount) > 200
)
SELECT c.first_name, c.last_name, hvc.total_spent
FROM customer c
JOIN high_value_customers hvc ON hvc.customer_id = c.customer_id
ORDER BY hvc.total_spent DESC;

Chaining multiple CTEs

Separate multiple CTEs with commas — each CTE can reference CTEs defined before it:

WITH
monthly_revenue AS (
  SELECT
    date_trunc('month', placed_at) AS month,
    SUM(total_amount) AS revenue
  FROM orders
  GROUP BY 1
),
revenue_with_growth AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month) AS revenue_change
  FROM monthly_revenue
)
SELECT *
FROM revenue_with_growth
ORDER BY month;

Writeable CTEs: modifying data

CTEs can contain INSERT, UPDATE, or DELETE with RETURNING:

-- Move old orders to an archive table atomically
WITH deleted AS (
  DELETE FROM orders
  WHERE placed_at < now() - interval '2 years'
  RETURNING *
)
INSERT INTO orders_archive SELECT * FROM deleted;

CTE vs subquery: when to use each

CTE: use when the same subquery would appear multiple times, or when readability matters for complex multi-step logic.
Subquery: fine for simple one-time filtering; the optimizer can often inline it more aggressively.
In PostgreSQL 12+, CTEs are inlined by default. Add WITH cte AS MATERIALIZED (…) to force materialization (useful when you want the planner to treat it as a fence).

Production tips

Use CTEs to make long queries reviewable — each CTE is a named, testable step.
In PostgreSQL 12+, CTEs are inlined (not materialized) by default, so they have no inherent performance overhead.
Use MATERIALIZED keyword when you explicitly want the CTE result computed once and reused, not re-planned inline.
For recursive queries (hierarchical data, graph traversal), use recursive CTEs with WITH RECURSIVE.

Reference: PostgreSQL documentation — WITH Queries (Common Table Expressions).

Frequently Asked Questions

What is a CTE (Common Table Expression) in PostgreSQL?

A CTE is a temporary named result set created with the WITH keyword before a SELECT, INSERT, UPDATE, or DELETE statement. The named CTE can be referenced in the main query (and in subsequent CTEs in the same WITH clause). CTEs do not persist beyond the single query execution. They are useful for breaking complex queries into readable named steps, avoiding repeated subqueries, and implementing recursive queries.

What is the difference between a CTE and a subquery?

A subquery is an inline query nested inside another query — it can only be referenced once at its location. A CTE is named and can be referenced multiple times in the same query, which avoids repeating identical subqueries. CTEs also improve readability: chained CTEs read top-to-bottom like procedural steps. In PostgreSQL 12+, CTEs are "inlined" by default (the planner optimizes them like subqueries), so there is no automatic performance penalty for using CTEs.

Can I use CTEs for INSERT, UPDATE, or DELETE?

Yes. PostgreSQL supports "writeable CTEs" — a WITH clause can contain INSERT, UPDATE, or DELETE statements that return rows using RETURNING. This lets you chain data modifications: for example, DELETE rows from one table and INSERT them into another in a single atomic statement. Each DML CTE is executed once, regardless of how many times it is referenced.

When should I use a CTE vs a subquery vs a temporary table?

Use a CTE when: the subquery is referenced multiple times, or readability is the priority for complex logic. Use a subquery for simple one-time filtering or when you need the optimizer to inline the logic. Use a temporary table when: the intermediate result is large and needs an index, or you need to reference the result across multiple separate queries. Temporary tables materialize results to disk; CTEs (in PostgreSQL 12+) are usually inlined.