Mastering PostgreSQL 9.6

Using WITH clause - common table expressions

Common table expressions are a nice way to execute things inside an SQL statement only once. PostgreSQL will execute all WITH clauses and allows you to use the results all over the query.

Here is a simplified example:

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 common table extension (CTE) calculates the average value of the time series generated by generate_series ...