我有两张以下表格(即每个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 ...)
但是我无法弄清楚如何在foo中为每一行创建一个关联行的副本,并将该条的id插入到新的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,bar_id=102) ..... bar(id=102,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,bar_id=104) ..... bar(id=104,z=16) foo(id=5,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) );
先插入值 – 栏.
如果您在这样的问题中提供了测试数据,这将是非常有帮助的！
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,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;
查询：
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,bar_id)
SELECT a.x,a.y,b.bar_id
FROM a
JOIN b USING (z);
这应该是你最后一次更新所描述的.
查询假定z是UNIQUE.如果z不是唯一的,它会变得更加复杂.在这种情况下,请使用窗口函数row_number(),参考Query 2 in this related answer获取一个现成的解决方案.
另外,考虑用一个统一的表格替换foo和bar之间的1：1关系.
数据修改CTE
第二回答更多信息.
如果要在单个查询中向foo和bar添加行,则可以使用Postgresql 9.1之后的data modifying CTE：
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,bar_id)
SELECT col1,bar_id
FROM x;
我从foo绘制值,将它们插入到bar中,将它们与自动生成的bar_id一起返回,并将其插入到foo中.您也可以使用任何其他数据.
这是一个working demo to play with on 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,就像任何其他 – 可以包括你插入的表.首先读取行,然后插入.
