WITH clause—SQL syntax and semantics
Syntax
The with_clause and common_table_expression diagrams are reproduced from the section that describes the SELECT statement.
with_clause ::= [ WITH [ RECURSIVE ]
{ common_table_expression [ , ... ] } ]
common_table_expression ::= name [ ( name [ , ... ] ) ] AS (
{ select
| values
| insert
| update
| delete } )
with_clause
common_table_expression
Semantics
The WITH clause lets you name a SQL statement which might be one of SELECT, VALUES, INSERT, UPDATE, or DELETE. You can then refer to the statement by name (just as if it were a schema-level view that names a SELECT statement) in a subsequent CTE definition or in the statement's final section. A very common use of the data-changing statements in the WITH clause is when they have a RETURNING clause. Then when you later refer to that statement by name, it behaves the same as if it were a named SELECT statement.
The uniqueness scope for the name of the CTE is the relation names defined in a particular WITH clause. You can define column aliases compactly in the optional parenthesized list that follows the name of the CTE, just as you can with a schema-level view. See the section Example where a CTE defined in the WITH clause itself has a WITH clause.
Notice that the WITH clause is legal in the SELECT statement and in each of the kinds of data-changing statements, but not in the VALUES statement. See the syntax diagrams for SELECT, VALUES, INSERT, UPDATE, and DELETE.
The recursive CTE is explained in a dedicated section.
Example that uses three data-changing CTEs and a SELECT CTE in the WITH clause
First, create some test tables and inspect the contents.
set client_min_messages = warning;
do $body$
declare
table_names constant text[] := array['t1', 't2', 't3'];
drop_table constant text := '
drop table if exists ? cascade';
create_table constant text := '
create table ?(k int primary key, v int)';
insert_table constant text := '
insert into ?(k, v)
select g.v, g.v*2 from generate_series($1, $2) as g(v)';
t text not null := '';
i int not null := 1;
n constant int := 2;
begin
foreach t in array table_names loop
execute replace(drop_table, '?', t);
execute replace(create_table, '?', t);
execute replace(insert_table, '?', t) using i, i + n;
i := i + n + 1;
end loop;
end;
$body$;
select 't1'::text as name, k, v from t1
union all
select 't2'::text as name, k, v from t2
union all
select 't3'::text as name, k, v from t3
order by 1, 2;
This is the result:
name | k | v
------+---+----
t1 | 1 | 2
t1 | 2 | 4
t1 | 3 | 6
t2 | 4 | 8
t2 | 5 | 10
t2 | 6 | 12
t3 | 7 | 14
t3 | 8 | 16
t3 | 9 | 18
Now execute the example query. Notice that the WITH clause defines an INSERT CTE, an UPDATE CTE, a DELETE CTE, and a SELECT CTE. Each of the data-changing CTEs has a RETURNING clause; and the SELECT CTE accesses the unions returned by each of these.
with
i as (
insert into t1(k, v) values (21, 17), (31, 42) returning k, v),
u as (
update t2 set v = 99 where k in (4, 5) returning k, v),
d as (
delete from t3 where k in (8, 9) returning k, v),
s as (
select 'inserted into t1'::text as action, k, v from i
union all
select 'udated in t2'::text as action, k, v from u
union all
select 'deleted from t3'::text as action, k, v from d)
select action, k, v from s order by k;
This is the result:
action | k | v
------------------+----+----
udated in t2 | 4 | 99
udated in t2 | 5 | 99
deleted from t3 | 8 | 16
deleted from t3 | 9 | 18
inserted into t1 | 21 | 17
inserted into t1 | 31 | 42
Check that the result is as expected in the affected tables, "t1", "t2", and "t3":
select 't1'::text as name, k, v from t1
union all
select 't2'::text as name, k, v from t2
union all
select 't3'::text as name, k, v from t3
order by 1, 2;
This is the result:
name | k | v
------+----+----
t1 | 1 | 2
t1 | 2 | 4
t1 | 3 | 6
t1 | 21 | 17
t1 | 31 | 42
t2 | 4 | 99
t2 | 5 | 99
t2 | 6 | 12
t3 | 7 | 14
Example that uses a VALUES CTE and a SELECT CTE in the WITH clause
First, clear out the data from table "t1" from the previous demonstration.
truncate table t1;
Now use a WITH clause as part of an INSERT statement.
with
a1 as (
select g.v as k, g.v*2 as v
from generate_series(1, 3) as g(v)),
a2 as (
values (10, 17), (11, 42), (12, 99))
insert into t1(k, v)
select k, v from a1
union all
select column1 as k, column2 as v from a2;
select k, v from t1 order by k;
This is the result:
k | v
----+----
1 | 2
2 | 4
3 | 6
10 | 17
11 | 42
12 | 99
Example where a CTE defined in the WITH clause itself has a WITH clause
The following SQL statement uses two WITH clauses. Each defines its own scope for the names of the CTEs that they define. Notice that the name "colliding", as the name of a relation, is defined in three different scopes: schema scope; the scope of the outer WITH clause; and the scope of the inner WITH clause that the outer one begins with. For good measure, it's also used in a different namespace: as the name of a column, or column alias.
The outer WITH clause simply cannot see the names that are defined in the inner WITH clause. But each of these WITH clauses can see a schema-level relation with a name that collides with a name defined in that WITH clause by qualifying it with the schema name. Column names are always defined within the scope of the relation that contains them.
The example assumes that a user called "u1" exists and that a database called "demo" exists. Change these names in the example to match the names that are available to you.
\c demo u1
set client_min_messages = warning;
drop table if exists colliding cascade;
create table u1.colliding(colliding text primary key);
insert into u1.colliding(colliding) values ('goodbye'), ('world');
with
colliding(colliding) as (
with
a(colliding) as (select 'Hello'),
colliding(colliding) as (select colliding from u1.colliding where colliding like 'w%')
select ((select colliding from a)||' '||(select colliding from colliding))
)
select
(select colliding from u1.colliding where colliding like 'g%')||'—'||colliding
as colliding
from colliding;
This is the result:
colliding
---------------------
goodbye—Hello world