在单个查询中将行插入到多个表中，从涉及的表中进行选择 [英] INSERT rows into multiple tables in a single query, selecting from an involved table

问题描述

我有两个以下形式的表格(即，每个 foo 都链接到一个栏).

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

使用嵌套查询可以轻松复制 foo 中满足特定条件的行:

It's easy to copy rows in foo which meet a particular condition using a nested query:

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

但我不知道如何为 foo 中的每一行制作 bar 中关联行的副本并插入 bar<的 id/code> 到新的 foo 行.有没有办法在单个查询中做到这一点?

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?

期望结果的具体例子:

-- 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 获得更多信息之后.考虑这个演示:

Final version

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

插入值 - bar 首先.
如果您像这样在问题中提供测试数据，那将会非常有帮助！

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

检查:

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

查询:

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.

查询假设 z 是 UNIQUE.如果 z 不是唯一的，它会变得更复杂.有关使用窗口函数 row_number() 的现成解决方案，请参阅此相关答案中的查询 2在这种情况下.

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.

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

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

了解更多信息后的第二个答案.

Second answer after more info.

如果您想在单个查询中向 foo 和 bar 添加行，您可以使用 数据修改 CTE 自 PostgreSQL 9.1:

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;

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

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.

这是一个 在 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 ...

并且您可以加入您在 SELECT 中插入的表:

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

它只是一个像任何其他选择一样的 SELECT - 可以包含您要插入的表.首先读取行，然后插入.

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.

这篇关于在单个查询中将行插入到多个表中，从涉及的表中进行选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！