将行插入多个表在单个查询中，从一个复杂的表选择将行插入多个表在单个查询中，从一个复杂的表选择(INS

我有以下形式（即每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 ...)

但我无法弄清楚如何使相关行的拷贝在bar中的每一行foo和插入的ID bar到新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,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)

Answer 1:

最终版本

......之后，从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)
);

插入值- bar第一。
如果你在你这样的问题，提供的测试数据将是非常有帮助 ！

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

设置序列的当前值或我们得到重复键违规行为：

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

这应该做你的最后一次更新描述。

该查询假设z是UNIQUE 。 如果z不是唯一的，它变得更加复杂。 请参阅查询2在这个相关答案使用窗口功能的现成的解决方案row_number()在这种情况下。

此外，考虑更换1：1之间的关系 foo和bar有一个统一的表格。

---

数据修改CTE

之后更多信息第二个答案。

如果你想添加行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;

我得出的值从foo ，在将它们插入bar ，让他们有一个自动生成的一起返回bar_id并插入到foo 。 你可以使用任何其他数据了。

这是一个工作演示上sqlfiddle一起玩 。

---

基本

原来的答案与之前的澄清基本信息。
其基本形式是：

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

没有括号需要。 你可以做任何表相同

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

你可以加入到你插入的选择表：

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就像任何其他 - 可以包括要插入到表中。 该行首先读取，然后再插入。

Answer 2:

如果id的bar是串行的，并有默认值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

文章来源: INSERT rows into multiple tables in a single query, selecting from an involved table