How to Use CTEs in PostgreSQL

How do I create and use CTEs in PostgreSQL?

A Common Table Expression (CTE) is a temporary, named result set you can reference within a single PostgreSQL statement to simplify complex logic and enable recursion.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is a CTE in PostgreSQL?

A Common Table Expression (CTE) is a temporary, named result set that lasts only for one SQL statement.It boosts readability, supports recursion, and prevents repeating subqueries.

Why choose a CTE over subqueries?

CTEs let you reference the same derived data multiple times, chain logical steps, and give each step a descriptive name—making maintenance far easier than nested subqueries.

How do I write a basic CTE?

Begin with WITH, name the CTE, place the query in parentheses, then write the main query that uses it.

WITH recent_orders AS ( SELECT id, customer_id, total_amount FROM Orders WHERE order_date >= CURRENT_DATE - INTERVAL '30 days' ) SELECT * FROM recent_orders;

Can I chain multiple CTEs?

Yes.Separate each CTE with commas. Later CTEs can query earlier ones, forming a clear data pipeline.

When is RECURSIVE needed?

Add RECURSIVE after WITH when the CTE must reference itself, useful for hierarchies like category trees or referral chains.

What performance tips matter most?

Filter rows early inside the CTE, project only needed columns, index join keys, and use MATERIALIZED or NOT MATERIALIZED (PostgreSQL 12+) to guide planner choices.

Why How to Use CTEs in PostgreSQL is important

How to Use CTEs in PostgreSQL Example Usage


WITH customer_totals AS (
    SELECT c.id,
           c.name,
           SUM(o.total_amount) AS lifetime_spend
    FROM Customers c
    JOIN Orders o ON o.customer_id = c.id
    GROUP BY c.id, c.name
),
most_recent_product AS (
    SELECT o.customer_id,
           p.name AS product_name,
           ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date DESC) AS rn
    FROM Orders o
    JOIN OrderItems oi ON oi.order_id = o.id
    JOIN Products p ON p.id = oi.product_id
)
SELECT ct.id,
       ct.name,
       ct.lifetime_spend,
       mr.product_name
FROM customer_totals ct
LEFT JOIN most_recent_product mr
       ON mr.customer_id = ct.id AND mr.rn = 1
WHERE ct.lifetime_spend > 5000
ORDER BY ct.lifetime_spend DESC;

How to Use CTEs in PostgreSQL Syntax


WITH [RECURSIVE] cte_name [(column_list)] AS (
    cte_query  -- any SELECT, INSERT, UPDATE, DELETE, or VALUES
),
cte_name2 AS (
    ...
)
main_query;

Common Mistakes

Selecting * inside CTEs inflates result size and slows joins. Fix by projecting only required columns to reduce I/O and memory.
Forgetting to add RECURSIVE causes self-referencing CTEs to fail. Always place the RECURSIVE keyword right after WITH for hierarchical queries.