How to Use CTEs in PostgreSQL

Galaxy Glossary

How do I use CTEs (WITH clause) in PostgreSQL?

A Common Table Expression (CTE) lets you define a temporary, reusable result set within a query using the WITH clause.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is a CTE and why use it?

A Common Table Expression (CTE) is a named subquery defined with WITH. It simplifies complex logic, improves readability, and allows recursion without creating temporary tables.

How do I write a basic CTE?

Start with WITH alias AS (subquery), then reference alias in the main query.Place the CTE before the main SELECT, INSERT, UPDATE, or DELETE.

Example: Filter recent customers

The CTE recent_customers selects customers created this month, making the outer query easier to read.

Can I chain multiple CTEs?

Yes. Separate each CTE with a comma. Later CTEs can reference earlier ones, enabling step-by-step transformations.

How do recursive CTEs work?

Add the RECURSIVE keyword after WITH. Provide an anchor member and a recursive member joined by UNION ALL.PostgreSQL repeats the recursive member until no new rows appear.

When should I use CTEs in ParadeDB?

Use CTEs for hierarchical data, multi-step aggregations, or to break down long JOIN chains. ParadeDB’s PostgreSQL core executes CTEs efficiently, especially when materialize is disabled in v12+.

Best practices

1) Keep CTEs short and purpose-driven. 2) Name them descriptively. 3) Avoid overly large result sets; consider temp tables if reused across many queries.

Common mistakes

See below.

Why How to Use CTEs in PostgreSQL is important

How to Use CTEs in PostgreSQL Example Usage


-- Identify top-selling products in the last month
WITH recent_sales AS (
    SELECT oi.product_id, oi.quantity
    FROM Orders o
    JOIN OrderItems oi ON oi.order_id = o.id
    WHERE o.order_date >= current_date - INTERVAL '30 days'
),
product_totals AS (
    SELECT p.id, p.name, SUM(rs.quantity) AS units_sold
    FROM Products p
    JOIN recent_sales rs ON rs.product_id = p.id
    GROUP BY p.id, p.name
)
SELECT *
FROM product_totals
ORDER BY units_sold DESC
LIMIT 10;

How to Use CTEs in PostgreSQL Syntax


WITH [RECURSIVE] cte_name [(column1, ...)] AS (
    SELECT ...        -- anchor or non-recursive subquery
    [UNION ALL ...]   -- recursive member (optional)
)
[, another_cte AS (...)]
MAIN_QUERY;

-- In ParadeDB ecommerce context
WITH recent_orders AS (
    SELECT id, customer_id, total_amount
    FROM Orders
    WHERE order_date >= current_date - INTERVAL '30 days'
),
customer_totals AS (
    SELECT c.id, c.name, SUM(ro.total_amount) AS last_30d_spend
    FROM Customers c
    JOIN recent_orders ro ON ro.customer_id = c.id
    GROUP BY c.id, c.name
)
SELECT *
FROM customer_totals
ORDER BY last_30d_spend DESC;

Common Mistakes

Materializing unnecessarily: Pre-v12 PostgreSQL always materialized CTEs, hiding indexes and hurting performance. In v12+, use <code>MATERIALIZED</code> or <code>NOT MATERIALIZED</code> to control this. Benchmark both options.
Re-using heavy CTEs many times: Each reference may re-execute when NOT MATERIALIZED. Store large interim results in a temp table or use MATERIALIZED to cache once.