CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expression
Michiel Borkent
Michiel Borkent

Reputation: 34840

writeable common table expression and multiple insert statements

How do I write the following in a valid Postgres SQL query:

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

Upvotes: 6

Views: 4191

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

You can use CTEs, if you want this all in one statement:

with foo as (
      select * from ...
     ),
     b as (
      insert into bar
          select * from foo
          returning *
     )
insert into baz
    select * from foo;

Notes:

  • You should include column lists with insert.
  • You should specify the column names explicitly for the select *. This is important because the columns may not match in the two tables.
  • I always use returning with update/insert/delete in CTEs. This is the normal use case -- so you can get serial ids back from an insert, for instance.

Upvotes: 8

Mureinik
Mureinik

Reputation: 311978

A with clause's scope is just a single query. The only solution I can think of is to create a view and drop it when you're done inserting. It won't be completely ephemeral like a CTE, but there's no data duplication here - just a (relatively) cheap DDL operation:

-- Create the view
CREATE VIEW foo AS SELECT * FROM ...;

-- Perform the inserts
INSERT INTO bar SELECT * FROM foo;
INSERT INTO baz SELECT * FROM foo;

-- Drop the view when you're done
DROP VIEW foo;

Upvotes: 0

Related Questions