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)

Ответ 1

Окончательная версия

... после некоторой дополнительной информации от 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)
);

Сначала введите значения - bar.
Было бы очень полезно , если бы вы предоставили тестовые данные в своем вопросе следующим образом:

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;

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

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

В запросе предполагается, что z UNIQUE. Если z не является уникальным, он становится более сложным. Обратитесь к запросу 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, как и любой другой, который может включать в себя таблицу, в которую вы вставляете. Строки сначала считываются, а затем вставляются.

Ответ 2

id of 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 f.x, f.y, b.new_id
  FROM   s_foo f
  JOIN   s_bar b on b.id = f.bar_id
)
SELECT 1