我有两张以下表格(即每个foo都链接到一个吧).
@H_502_2@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中复制满足特定条件的行很容易：
@H_502_2@INSERT INTO foo (...) (SELECT ... FROM foo WHERE ...)但是我无法弄清楚如何在foo中为每一行创建一个关联行的副本,并将该条的id插入到新的foo行中.在单个查询中是否有任何方法？
具体示例：
@H_502_2@-- 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.考虑一下这个演示：
@H_502_2@-- 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) );先插入值 – 栏.
如果您在这样的问题中提供了测试数据,这将是非常有帮助的！
将序列设置为当前值,或者我们得到重复的密钥违规：
@H_502_2@SELECT setval('foo_id_seq',3); SELECT setval('bar_id_seq',102);检查：
@H_502_2@-- SELECT nextval('foo_id_seq') -- SELECT nextval('bar_id_seq') -- SELECT * from bar; -- SELECT * from foo;查询：
@H_502_2@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：
@H_502_2@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.
基本
原始答案与澄清前的基本信息.
基本形式是：
不需要括号
你可以对任何表做同样的事情
您可以加入到您插入到SELECT的表中：
@H_502_2@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,就像任何其他 – 可以包括你插入的表.首先读取行,然后插入.
