CODEWITHSUNDEEP
postgresqlsql-insertcommon-table-expression
Richard Wheeldon
Richard Wheeldon

Reputation: 1118

Does it make a difference if a CTE comes before or after an insert?

insert statements in postgres can use CTEs ("with" expressions). These can come either before or after the insert itself. Is there any difference between one style vs the other?

For example, given a simple couple of tables:

create table foo (x integer, y integer);
create table bar (x integer, y integer);
insert into foo (x,y) values (1,2),(3,4);

Is there any difference other than a cosmetic one between this:

with baz as (
    select * from foo
)
insert into bar
    select * from baz;

and this:

insert into bar
with baz as (
    select * from foo
)
select * from baz;

They seem to produce the same results with the same execution plans but is there a more subtle difference I'm missing?

Upvotes: 1

Views: 26

Answers (0)

Related Questions