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