writeable common table expression and multiple insert statements

common-table-expression, postgresql, sql

Solution

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.

Problem

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 ```

Original source