Common Table Expressions (CTEs) in PostgreSQL

A Common Table Expression (CTE) is a named temporary result set defined with the WITH keyword. CTEs make complex queries more readable by breaking them into logical, named steps.

Basic CTE

WITH active_customers AS (
  SELECT id, first_name, last_name, email
  FROM customers
  WHERE is_active = true
)
SELECT
  ac.first_name,
  ac.last_name,
  COUNT(o.id) AS order_count
FROM active_customers ac
JOIN orders o ON ac.id = o.customer_id
GROUP BY ac.id, ac.first_name, ac.last_name
ORDER BY order_count DESC;

The CTE active_customers runs first, and its result is available to the main query. You can think of it as a named subquery that sits at the top of your statement.

Chaining Multiple CTEs

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total) AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY DATE_TRUNC('month', created_at)
),
revenue_with_growth AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_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;

Each CTE can reference the ones defined before it, creating a pipeline of transformations. This is far more readable than deeply nested subqueries.

CTE with INSERT (Writable CTEs)

-- Archive old orders and return the count
WITH archived AS (
  DELETE FROM orders
  WHERE created_at < '2023-01-01'
  RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM archived;

PostgreSQL supports writable CTEs where the WITH clause contains INSERT, UPDATE, or DELETE statements. The RETURNING clause feeds the affected rows into the next step.

CTE vs Subquery

• Readability -- CTEs name each step, making the logic self-documenting
• Reuse -- a CTE can be referenced multiple times in the same query
• Performance -- in PostgreSQL 12+, CTEs are inlined by default (no longer optimization fences)
• Use subqueries when the nested logic is trivial and a CTE would add unnecessary verbosity