在单个查询中将行插入到多个表中，从涉及的表中进行选择

我有两个以下形式的表（即，每个 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并插入 idbar进入新的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)
);

插入值 -bar first.
像这样的测试数据对您的问题非常有帮助：

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

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

这应该执行您上次更新所描述的操作。

该查询假设z is UNIQUE. If z并不独特，它变得更加复杂。参考此相关答案中的查询 2 https://stackoverflow.com/a/10201349/939860使用窗口函数的现成解决方案row_number()在这种情况下。

另外，请考虑更换1:1关系之间foo and bar与一个统一的表。

数据修改CTE

更多信息后的第二个答案。

如果您想添加行foo and bar在单个查询中，您可以使用数据修改CTE https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING自从 PostgreSQL9.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并插入that into foo。您也可以使用任何其他数据。

这里有一个在 sqlfiddle 上玩的工作演示 http://sqlfiddle.com/#!1/5a52c/1.

Basics

原始答案在澄清之前包含基本信息。
基本形式是：

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与其他任何表一样 - 可以包括您要插入的表。首先读取行，然后插入。