WITH

The WITH clause allows you to define temporary result sets (Common Table Expressions or CTEs) that exist only for the duration of a query. CTEs can simplify complex queries by breaking them down into more manageable parts.

Basic usage

Create a CTE named numbers that contains a sequence of integers:

WITH numbers AS (
    SELECT * FROM generate_series(1, 5)
)
SELECT * FROM numbers;

This produces:

generate_series
1
2
3
4
5

Column aliases may be provided in the CTE definition which can then be used later in the query:

WITH c(a, b, c) AS (
    SELECT 4, 5, 6
)
SELECT c, b, a FROM c

If column names aren't provided, then the CTE will use the column names generated by the select expressions inside the CTE.

Multiple CTEs

You can define multiple CTEs in a single WITH clause by separating them with commas:

WITH
    cte1 AS (select_statement1),
    cte2 AS (select_statement2)
SELECT * FROM cte1 JOIN cte2 ON ...;

Create two CTEs and join them together:

WITH
    even_numbers AS (
        SELECT * FROM generate_series(2, 10, 2)
    ),
    odd_numbers AS (
        SELECT * FROM generate_series(1, 9, 2)
    )
SELECT e.generate_series AS even, o.generate_series AS odd
FROM even_numbers e JOIN odd_numbers o
ON e.generate_series = o.generate_series + 1;

This produces:

even	odd
2	1
4	3
6	5
8	7
10	9

CTEs can only reference CTEs defined earlier in the query.

Materialized CTEs

By default, a CTE is evaluated each time it is referenced in the query. You can use the MATERIALIZED to materialize the results which will then be reused instead of executing the CTE multiple times.

For example, the result of the numbers CTE will be computed only once, even though we're referencing numbers more than once in the query.

WITH numbers AS MATERIALIZED (
    SELECT * FROM generate_series(1, 100)
)
SELECT COUNT(*)
FROM numbers n1, numbers n2, numbers n3

This can be useful for CTEs which are expensive to compute, or CTEs which may not be deterministic.

For example, if we have a CTE that calls random, we can ensure that CTE is materialized meaning we'll get the same results no matter how many times the CTE is referenced:

WITH c AS MATERIALIZED (
    SELECT random() * 100 AS a
)
SELECT c1.a = c2.a FROM c AS c1, c AS c2;

The result of this query will return true as we're only executing random once, and reusing the result.