How to Use CTEs in PostgreSQL

How do I write a CTE in PostgreSQL?

A Common Table Expression (CTE) is a temporary, named result set defined with WITH that can be referenced within the same query.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

PostgreSQL CTE Guide

WITH lets you define reusable, readable subqueries that run once per statement. Learn syntax, best practices, and real-world examples.

What problems do CTEs solve?

CTEs simplify deeply nested subqueries, allow self-referencing recursion, and improve query readability. They also enable multiple references to the same derived table without repeating code.

How do I write a basic CTE?

Start with WITH, assign a name, optionally list columns, then write the subquery. The main SELECT that follows can reference that name like a normal table.

Example

WITH recent_orders AS ( SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '30 days' ) SELECT customer_id, COUNT(*) AS orders_last_30 FROM recent_orders GROUP BY customer_id;

When should I prefer CTEs over subqueries?

Use CTEs when the same derived data is reused, when recursion is needed, or when splitting complex logic into logical blocks helps maintainability and debugging.

Can CTEs be recursive?

Yes. Add the RECURSIVE keyword and provide an anchor query plus a UNION ALL for the recursive part. PostgreSQL iterates until no new rows are produced.

Recursive example: category tree

WITH RECURSIVE cat_tree AS ( SELECT id, parent_id, name, 1 AS depth FROM categories WHERE parent_id IS NULL -- anchor UNION ALL SELECT c.id, c.parent_id, c.name, ct.depth + 1 FROM categories c JOIN cat_tree ct ON c.parent_id = ct.id ) SELECT * FROM cat_tree ORDER BY depth, name;

How do multiple CTEs work?

Separate CTE definitions with commas. Later CTEs may reference earlier ones, enabling step-by-step data transformations.

Best practices for CTEs

Keep each CTE focused on a single task. Use descriptive names. Analyze performance with EXPLAIN; PostgreSQL may inline or materialize a CTE depending on the version and cost.

Performance tips

In PostgreSQL 12+, non-recursive CTEs can be inlined. Use MATERIALIZED/NOT MATERIALIZED hints to control behavior when needed. Avoid huge intermediate results; index source tables instead.

Common mistakes

See below for frequent pitfalls and fixes.

Why How to Use CTEs in PostgreSQL is important

How to Use CTEs in PostgreSQL Example Usage


WITH sales_totals AS (
    SELECT product_id, SUM(amount) AS total
    FROM   sales
    GROUP  BY product_id
)
SELECT p.name, s.total
FROM   products p
JOIN   sales_totals s USING (product_id)
ORDER  BY s.total DESC;

How to Use CTEs in PostgreSQL Syntax


WITH [RECURSIVE] cte_name [(column_list)] AS (subquery)
    [, cte2 AS (subquery2) ...]
SELECT ... FROM cte_name ...;

Common Mistakes

Defining a CTE but never referencing it in the final query. PostgreSQL still executes the subquery, wasting resources. Always select from each CTE or remove it.
Assuming CTEs are always optimized away. Before v12 they materialize, which can slow queries. In v12+ use NOT MATERIALIZED or rewrite as a subquery when you need inlining.

Frequently Asked Questions (FAQs)

Are CTEs indexed?

No. A CTE is an in-memory result set created at runtime. It uses indexes on underlying tables during execution but cannot itself be indexed.

Can I update data inside a CTE?

Yes. A writable CTE uses INSERT, UPDATE, or DELETE inside the CTE and a RETURNING clause to feed the outer query. Use cautiously for clarity.

How many CTEs can I chain?

PostgreSQL imposes no hard limit beyond available memory. However, readability and maintenance suffer after 4-5 levels; consider breaking the query or using views.