INSERT строки в несколько таблиц в одном запросе, выбирая из вовлеченной таблицы

У меня есть две таблицы следующего вида (т. Е. Каждый foo связан ровно с одним баром).

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

Легко скопировать строки в foo которые удовлетворяют определенному условию, используя вложенный запрос:

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

Но я не могу понять, как сделать копию связанной строки в bar для каждой строки в foo и вставить идентификатор bar в новую строку foo . Есть ли способ сделать это в одном запросе?

Конкретный пример желаемого результата:

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

Окончательный вариант

… после некоторой дополнительной информации от OP. Рассмотрим эту демонстрацию:

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

Установите последовательности на текущие значения или мы получим дубликаты ключевых нарушений:

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

Проверки:

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

Запрос:

 WITH a AS ( SELECT fx, fy, bar_id, bz 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 ax, ay, b.bar_id FROM a JOIN b USING (z); 

Это должно делать то, что описывает ваше последнее обновление.

В запросе предполагается, что z UNIQUE . Если z не является уникальным, он становится более сложным. Обратитесь к Query 2 в этом связанном ответе для готового решения с помощью оконной функции row_number() в этом случае.

Кроме того, рассмотрите замену соотношения 1: 1 между foo и bar с единой объединенной таблицей.


Изменение данных CTE

Второй ответ после более подробной информации.

Если вы хотите добавить строки в foo и bar в одном запросе, вы можете использовать данные, модифицирующие CTE с 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; 

Я рисую значения из foo , вставляю их в bar , возвращаю их вместе с автоматически сгенерированным bar_id и вставляем их в foo . Вы также можете использовать любые другие данные.

Ниже приведена рабочая демонстрация для игры на sqlfiddle .


основы

Оригинальный ответ с базовой информацией перед разъяснениями.
Основная форма:

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

Не требуется скобок. Вы можете сделать то же самое с любой таблицей

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

И вы можете присоединиться к таблице, которую вы вставляете в 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 

Это просто SELECT, как и любой другой, который может включать таблицу, в которую вы вставляете. Строки сначала считываются, а затем вставляются.

id bar является серийным и имеет значение по умолчанию nextval('bar_id_seq'::regclass) вы можете вызвать его вручную, чтобы получить новые идентификаторы в 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 fx, fy, b.new_id FROM s_foo f JOIN s_bar b on b.id = f.bar_id ) SELECT 1