How to Use CTE in PostgreSQL

How do I write a CTE in PostgreSQL?

The WITH clause lets you define a temporary, named result set (CTE) that can be referenced multiple times in the same statement.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does a CTE do in PostgreSQL?

A Common Table Expression (CTE) created with the WITH clause produces a temporary, reusable result set that exists only for the duration of the query. It simplifies complex subqueries, improves readability, and supports recursion.

How do I write a basic CTE?

Start with WITH cte_name AS (SELECT ...), then write your main query referencing cte_name. The CTE runs first; its rows feed the outer query.

When should I use multiple CTEs?

Use multiple CTEs when the main query requires several intermediate steps—calculations, filters, or aggregates—that would otherwise repeat or clutter the statement. Separate each CTE with a comma.

Example: Filter customers then join orders

This pattern isolates logic: one CTE to filter recent customers, another to total their orders, followed by the final SELECT.

Can CTEs replace subqueries?

Yes for readability and reuse, but CTEs are not always faster. PostgreSQL materializes large or recursive CTEs unless you add WITH ... AS MATERIALIZED/NOT MATERIALIZED (v12+).

How do recursive CTEs work?

Add RECURSIVE after WITH. Supply an anchor SELECT and a UNION ALL that references the CTE itself. PostgreSQL iterates until no new rows appear—ideal for hierarchies.

Best practices for CTEs?

1) Name CTEs descriptively. 2) Keep each CTE focused on one task. 3) Use NOT MATERIALIZED for small CTEs that benefit from inlining. 4) Index base tables, not CTEs; they’re temporary.

Why How to Use CTE in PostgreSQL is important

How to Use CTE in PostgreSQL Example Usage


-- 1. Recent customers (last 30 days)
WITH recent_customers AS (
    SELECT id, name, email
    FROM Customers
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
),

-- 2. Order totals per customer
customer_totals AS (
    SELECT o.customer_id, SUM(o.total_amount) AS total_spent
    FROM Orders o
    WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY o.customer_id
)

SELECT rc.name, rc.email, ct.total_spent
FROM recent_customers rc
LEFT JOIN customer_totals ct ON ct.customer_id = rc.id
ORDER BY ct.total_spent DESC NULLS LAST;

How to Use CTE in PostgreSQL Syntax


WITH cte_name [(column_list)] AS (
    SELECT columns
    FROM table
    [WHERE condition]
    [GROUP BY ...]
)
SELECT ...
FROM cte_name
JOIN other_table ON ...;

-- Multiple CTEs
WITH first_cte AS (
    ...
),
second_cte AS (
    ...
)
SELECT ... FROM first_cte JOIN second_cte USING (...);

-- Recursive CTE (simplified)
WITH RECURSIVE cte_name AS (
    SELECT anchor_columns
    FROM table WHERE anchor_condition
    UNION ALL
    SELECT t.*
    FROM table t
    JOIN cte_name c ON t.parent_id = c.id
)
SELECT * FROM cte_name;

Common Mistakes

Using a CTE but selecting from it before it is defined. Fix: Always place the <code>WITH</code> clause before the main <code>SELECT</code>.
Forgetting the comma between multiple CTEs causes a syntax error. Fix: Separate each CTE definition with a comma, except the last one.

Frequently Asked Questions (FAQs)

Do CTEs always improve performance?

No. CTEs make queries readable but can be materialized, adding overhead. Use NOT MATERIALIZED or rewrite as subqueries when speed is critical.

What is the difference between CTE and temporary table?

CTEs live only for one statement and require no explicit cleanup, while temporary tables persist for a session and can be indexed or reused across queries.