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)

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.
It would be very helpful if you provided test data in your question like this!

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 draw 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.

Comments

[Chris Gessler]

Have you looked into updatable views with an insert rule? archives.postgresql.org/pgsql-admin/2006-05/msg00290.php

[Erwin Brandstetter]

Is the value for bar.z unique?

[foldl]

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.

[Erwin Brandstetter]

Why would you say that? I provided proof to the contrary.

[foldl]

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.

[Erwin Brandstetter]

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. ;)

[foldl]

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]

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.

[Erwin Brandstetter]

@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"?

[foldl]

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.

[Erwin Brandstetter]

@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.

[foldl]

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]

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]

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]

Or rather, it seems that this will work only if you're inserting the same values into bar as into foo.

[Erwin Brandstetter]

@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.

[foldl]

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.