在单个查询中将行插入到多个表中，从涉及的表中选择 [英] 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插入新的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不是唯一的，它将变得更加复杂.在这种情况下，请参考此相关答案中的查询2 ，以获得使用窗口函数row_number()的现成解决方案.
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.
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一起返回，然后将 that 插入到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屋！
