Mastering PostgreSQL 12 - Third Edition

Using the WITH clause – common table expressions

Common table expressions are a nice way to execute things inside an SQL statement, but only once. PostgreSQL will execute all the WITH clauses and allow us to use the results throughout the query.

Here is a simplified example of this:

test=# WITH x AS (SELECT avg(id)                   FROM generate_series(1, 10) AS id)    SELECT *, y - (SELECT avg FROM x) AS diff    FROM generate_series(1, 10) AS y    WHERE y > (SELECT avg FROM x);  y  | diff ----+--------------------   6 | 0.5000000000000000   7 | 1.5000000000000000   8 | 2.5000000000000000   9 | 3.5000000000000000  10 | 4.5000000000000000 (5 rows) 

In this example, the WITH clause's common table extension (CTE) calculates the average value of the time series generated ...