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

I have two tables of the following form (i.e., every foo is linked to exactly one bar).

我有以下两张表格(即，每个foo都链接到一个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:

使用嵌套查询很容易复制foo中满足特定条件的行:

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?

但是我不知道如何为foo中的每一行创建一个bar中的关联行并将bar的id插入到新的foo行中。在一个查询中有什么方法可以做到这一点吗?

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)

2 个解决方案

#1

Final version

... after some more info from OP. Consider this demo:

…在从opp获得更多信息后，考虑这个演示:

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

查询假定z是惟一的。如果z不是唯一的，就会变得更复杂。在本例中，使用窗口函数row_number()查询2获得一个就绪解决方案。

Also, consider replacing the 1:1 relation between foo and bar with a single united table.

另外，考虑用一个统一的表替换foo和bar之间的1:1关系。

---

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:

如果想在单个查询中向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;

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.

我从foo中提取值，插入到bar中，让它们与一个自动生成的bar_id一起返回并将其插入到foo中。您也可以使用任何其他数据。

Here is a working demo to play with on sqlfiddle.

这里有一个在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:

您可以连接到您在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.

它就像其他的选择一样——可以包含您要插入的表。首先读取行，然后插入行。

#2

if id of bar is serial and have default value nextval('bar_id_seq'::regclass) you can manually call this function to get new ids in cte

如果bar的id是串行的，并且具有默认值nextval('bar_id_seq'::regclass)，您可以手动调用该函数，在cte中获取新的id

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