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

Question

I have two tables of the following form (i.e., every foo is linked to exactly one bar).

CREATE TABLE foo (
    id INTEGER PRIMARY KEY,
    x INTEGER NOT NULL,
    y INTEGER NOT NULL,
    ...,
    bar_id INTEGER UNIQUE NOT NULL,
    FOREIGN key (bar_id) REFERENCES bar(id)
);

CREATE TABLE bar (
    id INTEGER PRIMARY KEY,
    z INTEGER NOT NULL,
    ...
);

It's easy to copy rows in foo which meet a particular condition using a nested query:

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

But I can't figure out how to make a copy of the associated row in bar for each row in foo and insert the id of bar into the new foo row. Is there any way of doing this in a single query?

Concrete example of desired result:

-- Before query:

foo(id=1,x=3,y=4,bar_id=100)  .....  bar(id=100,z=7)
foo(id=2,x=9,y=6,bar_id=101)  .....  bar(id=101,z=16)
foo(id=3,x=18,y=0,bar_id=102) .....  bar(id=102,z=21)


-- Query copies all pairs of foo/bar rows for which x>3:

-- Originals
foo(id=1,x=3,y=4,bar_id=101)  .....  bar(id=101,z=7)
foo(id=2,x=9,y=6,bar_id=102)  .....  bar(id=102,z=16)
foo(id=3,x=18,y=0,bar_id=103) .....  bar(id=103,z=21)

-- "Copies" of foo(id=2,...) and foo(id=3,...), with matching copies of
-- bar(id=102,...) and bar(id=103,...)
foo(id=4,x=9,y=6,bar_id=104)  .....  bar(id=104,z=16)
foo(id=5,x=18,y=0,bar_id=105) .....  bar(id=105,z=21)

Have you looked into updatable views with an insert rule? http://archives.postgresql.org/pgsql-admin/2006-05/msg00290.php — Chris Gessler, May 06 '12 at 15:46
Well, thank you for your additional answer. I guess it is just not possible to do this in any general way in a single query. — foldl, May 06 '12 at 19:08
Well, there's a simple explanation. I said it before you added a link to the answer which doesn't assume that 'z' is unique :) It seems the only practical solution is to merge the two tables, however, as you suggest. — foldl, May 06 '12 at 19:18
Just means that the answer is assuming that for lack of information. Follow my link for a solution with non-unique `z`. As I said before: [you can do pretty much anything - as soon as you get a grasp on the code.](http://www.youtube.com/watch?v=SdkdQtlF-RU) ;) — Erwin Brandstetter, May 06 '12 at 19:24
Yes, I saw the link. Thanks. I was just saying that (at least in my case) it makes more sense to merge the two tables than write such complex queries. — foldl, May 06 '12 at 19:27

Erwin Brandstetter · Accepted Answer · 2023-08-09T04:37:37.127

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.

But this query won't copy the `bar` rows, right? The new `foo` rows will be linked to the old `bar` rows, rather than copies of them. — foldl, May 06 '12 at 16:25
@foldl: I am not following. This query will `INSERT` what ever the `SELECT` returns into the table `foo`. The new rows in `foo` are just like any other row. What do you mean by "linked"? — Erwin Brandstetter, May 06 '12 at 16:30
I just mean that the query won't add any new rows to `bar`. I want to insert a new row in `bar` for every new row inserted in `foo`. — foldl, May 06 '12 at 16:37
@foldl: The query add rows to foo, nothing else. You can only add rows to *one* table in a single INSERT statement in PostgreSQL. You can link multiple INSERTs together with the new data modifying CTEs. Will add an example to my answer if you clarify what you want to achieve **in your question**. Please edit the last paragraph. — Erwin Brandstetter, May 06 '12 at 16:47
Yes, I know that a single INSERT statement can only add rows to one table; that's why I was asking the question in the first place. I'm not really sure how to modify the question. I just want to copy a subset of the rows in `foo`, and make corresponding copies of rows in `bar` (so that the new rows in `foo` are appropriately linked to the new rows in `bar` via `bar_id`). — foldl, May 06 '12 at 16:54
Ah, thanks for this answer, very helpful! Annoyingly enough, I'd tried exactly this, but because I had parens around the inner "SELECT", the columns of foo weren't visible in my returning statement... — foldl, May 06 '12 at 17:22
Hmm, it's not clear to me how you copy the values from old rows in `foo` into new rows in `foo`, though. In the example, the new rows of `foo` draw their values from the new rows of `bar`, not from the original `foo` rows. — foldl, May 06 '12 at 18:00
Or rather, it seems that this will work only if you're inserting the same values into `bar` as into `foo`. — foldl, May 06 '12 at 18:08
@foldl: You can do pretty much anything. If you make clear what you want (**in your question**, like I requested), I can adapt my answer. I added a bit of explanation. — Erwin Brandstetter, May 06 '12 at 18:17
Ok thanks, I've added a concrete example of the desired result. The thing is that in my case it is not possible to, as it were, "move the value into `foo` via `bar`"; I hope the example will make it clearer what I mean by this. — foldl, May 06 '12 at 18:25

Matveev Dmitriy · Answer 2 · 2018-03-23T09:58:25.303

if id of bar is serial and have default value nextval('bar_id_seq'::regclass) you can manually call this function to get new ids in cte

with
s_bar as (
  SELECT id, z, nextval('bar_id_seq'::regclass) new_id
  FROM   bar
  WHERE  ...
),
s_foo as (
  SELECT x, y, bar_id
  FROM   foo
  WHERE  ...
),
i_bar as (
  INSERT INTO bar (id, z)
  SELECT new_id, z
  FROM   s_bar
),
i_foo as (
  INSERT INTO foo (x, y, bar_id)
  SELECT f.x, f.y, b.new_id
  FROM   s_foo f
  JOIN   s_bar b on b.id = f.bar_id
)
SELECT 1

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

2 Answers2

Final version

Data modifying CTE

Basics

Linked