How to Write a Common Table Expression in PostgreSQL

Common Table Expressions (CTEs) are a powerful feature in PostgreSQL that allow you to write cleaner and more modular SQL queries. This tutorial will guide you through what CTEs are, when to use them, and how to write them effectively.

What is a Common Table Expression?

A Common Table Expression (CTE) is a temporary result set that you can reference within a SQL statement. Think of it as creating a temporary table that only exists during the execution of that query. It helps make complex queries easier to read, maintain, and debug.

Basic Syntax

WITH cte_name AS (
    SELECT column1, column2
    FROM some_table
    WHERE condition
)
SELECT *
FROM cte_name;
                                

The WITH clause defines the CTE. You can then reference cte_name as if it were a table in your subsequent query.

Example: Simple CTE

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

Here, we first select orders from the last 30 days into the recent_orders CTE, then we count how many orders each customer placed.

Chaining Multiple CTEs

WITH recent_orders AS (
    SELECT id, customer_id, order_date
    FROM orders
    WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
),
customer_totals AS (
    SELECT customer_id, COUNT(*) AS total_orders
    FROM recent_orders
    GROUP BY customer_id
)
SELECT c.customer_id, c.total_orders, u.name
FROM customer_totals c
JOIN users u ON c.customer_id = u.id;
                                

You can chain multiple CTEs together to build modular query blocks.

When to Use CTEs

• To break down complex queries into manageable pieces
• To improve readability and maintainability
• When you need to reference the same subquery multiple times
• To use recursion (for hierarchical or tree-like data)

Tips and Best Practices

• Give your CTEs meaningful names to improve readability.
• Limit the number of CTEs in a single query to avoid confusion.
• Be aware of performance—sometimes materialized views or subqueries can be faster for large datasets.

Conclusion

Common Table Expressions are an excellent tool in PostgreSQL for simplifying and structuring your SQL queries. By using CTEs, you can write more modular, understandable, and maintainable SQL. Give them a try in your next query!