writeable common table expression and multiple insert statements

Question

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

Answer 1

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.