The WITH clause (Common Table Expression)
WITH clause (sometimes known as the common table expression) can be used as part of a
SELECT statement, an
INSERT statement, an
UPDATE statement, or a
DELETE statement. For this reason, the functionality is described in this dedicated section.
For example, a
WITH clause can be used to provide values for, say, an
INSERT like this:
set client_min_messages = warning; drop table if exists t1 cascade; create table t1(k int primary key, v int not null); with a(k, v) as ( select g.v, g.v*2 from generate_series(11, 20) as g(v) ) insert into t1 select k, v from a; select k, v from t1 order by k;
This is the result:
k | v ----+---- 11 | 22 12 | 24 13 | 26 14 | 28 15 | 30 16 | 32 17 | 34 18 | 36 19 | 38 20 | 40
Moreover, data-changing substatements (
UPDATE , and
DELETE) can be used in a
WITH clause and, when these use a
RETURNING clause, the returned values can be used in another data-changing statement like this:
set client_min_messages = warning; drop table if exists t2 cascade; create table t2(k int primary key, v int not null); with moved_rows as ( delete from t1 where k > 15 returning k, v) insert into t2(k, v) select k, v from moved_rows; ( select 't1' as table_name, k, v from t1 union all select 't2' as table_name, k, v from t2 ) order by table_name, k;
The central notion is that a
WITH clause lets you name one or more substatements (which each may be either
DELETE) so that you can then refer to such a substatement by name, either in a subsequent substatement in that
WITH clause or in the overall statement's final, main, substatement (which may be either
VALUES). In this way, a
WITH clause substatement is analogous, in the declarative programming domain of SQL, to a procedure or a function in the imperative programming domain of a procedural language.
Notice that a schema-level view achieves the same effect, for
VALUES, as does a substatement in a view with respect to the named substatement's use. However a schema-level view cannot name a data-changing substatement. In this way, a
WITH clause substatement brings valuable unique functionality. It also brings the modular programming benefit of hiding names, and the implementations that they stand for, from scopes that have no interest in them.
Finally, the use of a recursive substatement in a
WITH clause enables graph analysis. For example, an "employees" table often has a self-referential foreign key like "manager_id" that points to the table's primary key, "employee_id".
SELECT statements that use a recursive
WITH clause allow the reporting structure to be presented in various ways. For example, this result shows the reporting paths of employees, in an organization with a strict hierarchical reporting scheme, in depth-first order. See the section Pretty-printing the top-down depth-first report of paths.
emps hierarchy ---------------- mary fred alfie dick george john alice bill joan edgar susan
The remainder of this section has the following subsections:
A SQL statement that uses a
WITH clause sometimes gets a worse execution plan than the semantically equivalent statement that doesn’t use a
WITH clause. The explanation is usually that a “push-down” optimization of a restriction or projection hasn’t penetrated into the
WITH clause’s substatement. You can usually avoid this problem by manually pushing down what you’d hope would be done automatically into your spellings of the
WITH clause’s substatements.
Anyway, the ordinary good practice principle holds even more here: always check the execution plans of the SQL statements that your application issues, on representative samples of data, before moving the code to the production environment.