INSERT rows into multiple tables in a single query, selecting from an involved table

common-table-expression, insert, postgresql, sql

Solution

Final version

... after some more info from OP. Consider this demo:

-- DROP TABLE foo; DROP TABLE bar;

CREATE TEMP TABLE bar (
  id serial PRIMARY KEY  -- using a serial column!
, z  integer NOT NULL
);

CREATE TEMP TABLE foo (
  id     serial PRIMARY KEY  -- using a serial column!
, x      integer NOT NULL
, y      integer NOT NULL
, bar_id integer UNIQUE NOT NULL REFERENCES bar(id)
);

Insert values - `bar` first. Test data like this would be very helpful in your question:

INSERT INTO bar (id,z) VALUES
  (100, 7)
, (101,16)
, (102,21)
;

INSERT INTO foo (id, x, y, bar_id) VALUES
  (1, 3,4,100)
, (2, 9,6,101)
, (3,18,0,102)
;

Set sequences to current values or we get duplicate key violations:

SELECT setval('foo_id_seq', 3);
SELECT setval('bar_id_seq', 102);

Checks:

-- SELECT nextval('foo_id_seq')
-- SELECT nextval('bar_id_seq')
-- SELECT * from bar;
-- SELECT * from foo;

Query:

WITH a AS (
    SELECT f.x, f.y, bar_id, b.z
    FROM   foo f
    JOIN   bar b ON b.id = f.bar_id
    WHERE  x > 3
    ),b AS (
    INSERT INTO bar (z)
    SELECT z
    FROM   a
    RETURNING z, id AS bar_id
    )
INSERT INTO foo (x, y, bar_id)
SELECT a.x, a.y, b.bar_id
FROM   a
JOIN   b USING (z);

This should do what your last update describes.

The query assumes that `z` is `UNIQUE`. If `z` is not unique, it gets more complex. Refer to Query 2 in this related answer for a ready solution using the window function `row_number()` in this case.

Also, consider replacing the 1:1 relation between `foo` and `bar` with a single united table.

Data modifying CTE

Second answer after more info.

If you want to add rows to `foo` and `bar` in a single query, you can use a data modifying CTE since PostgreSQL 9.1:

WITH x AS (
    INSERT INTO bar (col1, col2)
    SELECT f.col1, f.col2
    FROM   foo f
    WHERE  f.id BETWEEN 12 AND 23  -- some filter
    RETURNING col1, col2, bar_id   -- assuming bar_id is a serial column
    )
INSERT INTO foo (col1, col2, bar_id)
SELECT col1, col2, bar_id
FROM   x;

I take values from `foo`, insert them in `bar`, have them returned together with an auto-generated `bar_id` and insert that into `foo`. You can use any other data, too.

Here is a working demo to play with on sqlfiddle.

Basics

Original answer with basic information before clarifications. The basic form is:

INSERT INTO foo (...)
SELECT ... FROM foo WHERE ...

No parenthesis needed. You can do the same with any table

INSERT INTO foo (...)
SELECT ... FROM bar WHERE ...

And you can join to the table you insert into in the SELECT:

INSERT INTO foo (...)
SELECT f.col1, f.col2, .. , b.bar_id
FROM   foo f
JOIN   bar b USING (foo_id);  -- present in foo and bar

It's just a `SELECT` like any other - that can include the table you are inserting into. The rows are first read, and then inserted.

Problem

Original source