Postgres CTE: Syntax, Examples, and Performance

A report query starts as one join, gains an aggregate, then picks up three filters and a second aggregation. Before long, the nested SQL is technically valid but difficult to review. A Postgres CTE gives each logical step a name, so you can reason about the query as a sequence instead of decoding it from the inside out.

What is a Postgres CTE? A common table expression is a named result set defined with WITH and available only to the statement that follows it. It behaves like a temporary table within that statement, but it is part of the query—not a database object that persists after the query ends.

This guide covers the syntax, realistic examples, recursive queries, planner behavior, and the point at which a subquery or temporary table is the better tool.

What is a Postgres CTE?

A CTE is an auxiliary statement placed before a primary SELECT, INSERT, UPDATE, DELETE, or MERGE. PostgreSQL's current `WITH` query documentation describes CTEs as temporary tables that exist for one query; the auxiliary statement can itself read or modify data.

The simplest shape is:

WITH cte_name AS (
    SELECT column_1, column_2
    FROM source_table
    WHERE condition
)
SELECT *
FROM cte_name;

The query inside the parentheses produces rows. The main query can then select from, join to, filter, or aggregate those rows by using cte_name as though it were a table.

That makes a CTE useful for two different jobs:

• Structure: turn a long query into named, reviewable stages.
• Capability: express recursive traversal or connect a data-changing statement to another operation through RETURNING.

A CTE is not automatically cached, indexed, or faster than an equivalent subquery. In modern PostgreSQL, the planner may fold a suitable CTE into its parent query, or it may materialize the CTE as a separate intermediate result. That distinction matters later when you tune performance.

Postgres CTE syntax for one or multiple steps

Start with the smallest useful case: name an intermediate result and consume it once.

WITH paid_orders AS (
    SELECT customer_id, order_total
    FROM orders
    WHERE payment_status = 'paid'
)
SELECT customer_id,
       SUM(order_total) AS lifetime_revenue
FROM paid_orders
GROUP BY customer_id;

paid_orders establishes the population. The outer query establishes the output grain: one row per customer. Separating those decisions can make review easier because a teammate can check the payment filter before inspecting the aggregation.

Name the output columns when it improves clarity

You can declare the CTE's column names after its name:

WITH monthly_revenue (month_start, revenue) AS (
    SELECT DATE_TRUNC('month', paid_at),
           SUM(order_total)
    FROM orders
    WHERE payment_status = 'paid'
    GROUP BY 1
)
SELECT month_start, revenue
FROM monthly_revenue
ORDER BY month_start;

Explicit names help when the expressions inside the CTE are long or when you want a stable interface between query stages. They do not change the underlying data types.

Chain multiple CTEs after one WITH

Separate multiple definitions with commas. A later CTE can reference an earlier one:

WITH paid_orders AS (
    SELECT customer_id, paid_at, order_total
    FROM orders
    WHERE payment_status = 'paid'
),
customer_totals AS (
    SELECT customer_id,
           SUM(order_total) AS lifetime_revenue,
           MAX(paid_at) AS last_order_at
    FROM paid_orders
    GROUP BY customer_id
)
SELECT customer_id, lifetime_revenue, last_order_at
FROM customer_totals
WHERE lifetime_revenue >= 1000
ORDER BY lifetime_revenue DESC;

Use one WITH, not one WITH per stage. Give each CTE a name that describes its rows—paid_orders or customer_totals—instead of its implementation, such as step_1.

When should you use a Postgres CTE?

Use a CTE when naming an intermediate result makes the query easier to validate or when the problem requires recursion. Good candidates include:

• Reusing the same derived result within a statement.
• Separating filtering, joining, and aggregation into stages with clear row grains.
• Comparing one aggregate with another, such as each region's revenue against the company total.
• Walking a hierarchy, dependency graph, bill of materials, or date sequence.
• Feeding rows returned by a data-modifying statement into another part of the same statement.

Do not add CTEs merely to make a short query look organized. Every named layer asks the reader to jump between a definition and its use. If a single derived table is read once and remains obvious in place, a subquery may be more direct.

Postgres CTE vs. subquery vs. temporary table

The three options solve overlapping but different problems:

Readability is the usual reason to choose a non-recursive CTE over a subquery. A temporary table is a stronger boundary: it costs an extra write and lifecycle management, but it can help when several later statements need the same large intermediate data or when that data benefits from an index.

Postgres CTE examples for analytics and hierarchies

The strongest CTE examples expose a decision that can be checked at each stage. The following patterns are small enough to adapt but realistic enough to show why the structure helps.

Example 1: calculate each region's share of revenue

This query builds a reusable regional aggregate, calculates the overall total from it, and combines the two:

WITH regional_revenue AS (
    SELECT region,
           SUM(order_total) AS revenue
    FROM orders
    WHERE payment_status = 'paid'
    GROUP BY region
),
company_revenue AS (
    SELECT SUM(revenue) AS revenue
    FROM regional_revenue
)
SELECT r.region,
       r.revenue,
       ROUND(100.0 * r.revenue / NULLIF(c.revenue, 0), 2)
           AS revenue_share_pct
FROM regional_revenue AS r
CROSS JOIN company_revenue AS c
ORDER BY r.revenue DESC;

The first stage has one row per region. The second has exactly one row. Writing those grains down before running the query makes it easier to catch accidental duplication from joins.

For a large production query, validate each stage independently during development, then run the complete statement. If the final result is intended for a dashboard, confirm that dimensions and metrics still have the expected grain before publishing it.

Example 2: rank products within each category

CTEs pair naturally with window functions because one stage can calculate a rank and the next can filter it:

WITH product_revenue AS (
    SELECT category_id,
           product_id,
           SUM(order_total) AS revenue
    FROM order_items
    GROUP BY category_id, product_id
),
ranked_products AS (
    SELECT category_id,
           product_id,
           revenue,
           ROW_NUMBER() OVER (
               PARTITION BY category_id
               ORDER BY revenue DESC, product_id
           ) AS revenue_rank
    FROM product_revenue
)
SELECT category_id, product_id, revenue
FROM ranked_products
WHERE revenue_rank <= 3
ORDER BY category_id, revenue_rank;

The secondary sort on product_id makes ties deterministic. The CTE also avoids repeating the revenue expression when assigning and filtering the rank.

Example 3: traverse an employee hierarchy recursively

A recursive CTE contains an anchor term, UNION or UNION ALL, and a recursive term that refers to the CTE itself. Although the syntax is recursive, PostgreSQL evaluates it iteratively through a working table, as the official recursive-query explanation details.

WITH RECURSIVE org_chart AS (
    SELECT employee_id,
           manager_id,
           employee_name,
           0 AS depth,
           ARRAY[employee_id] AS path
    FROM employees
    WHERE employee_id = 42

    UNION ALL

    SELECT e.employee_id,
           e.manager_id,
           e.employee_name,
           o.depth + 1,
           o.path || e.employee_id
    FROM employees AS e
    JOIN org_chart AS o
      ON e.manager_id = o.employee_id
    WHERE NOT e.employee_id = ANY(o.path)
)
SELECT employee_id, manager_id, employee_name, depth
FROM org_chart
ORDER BY path;

The anchor selects the root employee. Each iteration finds direct reports, while path both creates a depth-first output order and prevents a cycle from revisiting an employee. PostgreSQL also supports standard SEARCH and CYCLE clauses that generate ordering and cycle-tracking columns for recursive CTEs; these are useful when the explicit array pattern would obscure the business logic.

Always make termination visible. A missing join condition or cycle check can cause unbounded work. PostgreSQL's documentation suggests an outer LIMIT as a testing aid for uncertain recursion, but warns that this behavior is implementation-specific and can be defeated by an outer sort or join, so it is not a production safety mechanism.

Postgres CTE performance and materialization

A CTE is an organizational tool first, not a performance switch. The right question is not “Are CTEs fast?” but “What plan did PostgreSQL choose for this CTE with this data?”

For a non-recursive, side-effect-free CTE, PostgreSQL can fold the CTE into the parent query so both levels are optimized together. By default, that generally happens when the parent references the CTE once. A CTE referenced multiple times is normally evaluated separately, which avoids repeated computation but can prevent parent filters from reaching the underlying scan. These materialization rules and examples are documented by PostgreSQL.

You can state the intended boundary explicitly:

WITH candidate_orders AS NOT MATERIALIZED (
    SELECT order_id, customer_id, paid_at
    FROM orders
    WHERE payment_status = 'paid'
)
SELECT *
FROM candidate_orders
WHERE customer_id = 123;

NOT MATERIALIZED lets the planner merge a suitable CTE with the parent even when the default would keep it separate. This can expose selective filters and indexes, but it may repeat an expensive calculation when the CTE is referenced more than once.

MATERIALIZED forces separate evaluation. It can be helpful when you deliberately want to calculate an expensive or volatile result once, but it can also create a large intermediate result that the outer query later discards.

Verify the plan instead of relying on a rule of thumb

Use EXPLAIN to inspect the plan without executing the query. Use EXPLAIN (ANALYZE, BUFFERS) in a safe environment to compare estimates with actual row counts, timing, loops, and I/O. PostgreSQL's `EXPLAIN` guide notes that ANALYZE actually runs the statement, so do not casually apply it to INSERT, UPDATE, DELETE, or MERGE.

When comparing a CTE with an equivalent subquery, look for:

• A CTE Scan and the number of rows produced versus consumed.
• Filters applied after materialization instead of at the base-table scan.
• Repeated loops caused by NOT MATERIALIZED and multiple references.
• Large gaps between estimated and actual rows.
• Sorts or hashes spilling to disk and unexpectedly high buffer reads.

Test with representative data. A plan for a tiny development table may be completely different from the plan selected for production-scale cardinalities.

If your goal is to let business users ask questions without hand-building every query, GetInsights can translate a plain-English request into SQL against a connected database and return a chart or table. Keep the same review discipline: confirm the generated joins, filters, grain, and result, while read-only enforcement prevents the analytics workflow from issuing data-changing statements.

Data-modifying CTEs: useful, but easy to misuse

PostgreSQL allows INSERT, UPDATE, DELETE, and MERGE inside a WITH clause. RETURNING exposes the modified rows to the rest of the statement:

WITH archived_orders AS (
    DELETE FROM orders
    WHERE status = 'cancelled'
      AND created_at < CURRENT_DATE - INTERVAL '1 year'
    RETURNING *
)
INSERT INTO orders_archive
SELECT *
FROM archived_orders;

This pattern moves deleted rows into an archive in one statement. But the semantics deserve care: PostgreSQL says data-modifying CTEs execute exactly once and to completion, and sibling sub-statements share one snapshot while their update order is unpredictable. RETURNING is therefore the dependable way for one stage to communicate changed rows to another.

Avoid designing sibling statements that can modify the same row. The data-modifying CTE guidance warns that the result of trying to update the same row twice is not predictable.

For analytics, prefer a database role with SELECT only. Query-level conventions are useful, but database privileges—not a promise in application code—are the durable boundary against unintended writes.

Common Postgres CTE mistakes

Before shipping a query, check these failure modes:

1. Treating a CTE as a performance optimization. Measure the actual plan; readability and speed are separate outcomes.
2. Selecting more columns or rows than the next stage needs. Reduce the intermediate result early when semantics allow it.
3. Hiding the row grain. Name CTEs after what one row represents and verify counts after joins.
4. Reusing a large CTE without checking materialization. Multiple references may create a large intermediate result; NOT MATERIALIZED may instead repeat work.
5. Writing recursion without an explicit stop or cycle strategy. Make the termination condition visible and test cyclic data.
6. Using `UNION` when `UNION ALL` is intended. UNION removes duplicate rows at each recursive step and changes both semantics and cost.
7. Assuming output order. Add an outer ORDER BY; the evaluation order of a recursive query is not a result-order guarantee.
8. Running `EXPLAIN ANALYZE` on a write query without protection. It executes the statement. Use an explicit transaction and rollback in a controlled environment when appropriate.

If the problem is simply formatting a deeply nested query, start by making its structure consistent with a SQL beautifier workflow. Then introduce CTEs only where a named stage makes the data flow easier to prove.

Frequently asked questions

Is a CTE better than a subquery in PostgreSQL?

Neither is universally better. A CTE is often easier to read when a result has a meaningful name, appears more than once, or participates in a sequence of stages; a subquery can be clearer when it is short and used once. Compare execution plans when performance matters because PostgreSQL may optimize the forms similarly or materialize the CTE separately.

Is a CTE better than a temporary table?

Use a CTE for one statement and a temporary table when several statements need the same intermediate data or when you need to index and analyze it. A temporary table adds creation, storage, and cleanup work, while a CTE remains part of one statement.

Can I update a CTE in PostgreSQL?

PostgreSQL can attach WITH to an UPDATE, and a CTE can supply rows or values through UPDATE ... FROM. PostgreSQL also permits a data-modifying statement inside the CTE itself, with RETURNING providing rows that other parts of the statement can consume.

When should I not use a CTE?

Skip a CTE when it adds a name without making a simple query clearer, when several statements need the intermediate result, or when the chosen materialization behavior creates avoidable work. Use a subquery for compact local logic and a temporary table for reusable, indexable intermediate data.

Does a CTE improve performance in PostgreSQL?

Not automatically. A CTE can avoid repeating an expensive calculation when materialized, but materialization can also block filter pushdown and create a large intermediate result. Use EXPLAIN and, in a safe environment, EXPLAIN (ANALYZE, BUFFERS) to decide.

Can a Postgres CTE be recursive?

Yes. WITH RECURSIVE combines an anchor query with a recursive query using UNION or UNION ALL. It is commonly used for trees and graphs, but it needs a termination condition and, when cycles are possible, explicit cycle detection.

Conclusion

Choose a Postgres CTE when a named intermediate result makes one statement easier to understand, test, or reuse—or when recursion is essential. Choose a subquery for compact local logic, and choose a temporary table when the intermediate data must survive across statements or benefit from its own index.

For an existing query, the next step is concrete: label the grain of each stage, rewrite only the confusing boundaries as CTEs, then compare the before-and-after plans with representative data. If the query will feed self-service reporting, follow the same validation steps in the SQL query builder guide.