将数据插入至某表中...从另一张表选择需要的数据...返回新记录的id映射。

Question

我正在使用 PostgreSQL 9.3。

我想复制一些数据库记录。由于我在表中使用自增的主键 id，所以我想从复制的记录中获取生成的 id 到原始记录之间的映射关系。例如，假设我有一个名为 posts 的表，其中有 2 条记录：

 [{'id': 1, 'title': 'first'}
, {'id': 2. 'title': 'second'}]

使用SQL：

INSERT INTO posts (title) SELECT title FROM posts RETURNING id, ??

我期待看到类似以下的映射：

 [{'id': 3, 'from_id': 1}
, {'id': 4, 'from_id': 2}]

任何想法如何填写上述的问号使其正常工作？非常感谢！

- fengye87

---

我认为你可以通过别名来完成它：FROM posts oldposts，然后在RETURNING子句中引用旧和新的别名。但看起来好像不行。有点棘手。 - Craig Ringer

@CraigRinger 是的，我也试过了。不知道为什么它不能那样工作。 - fengye87

这段程序相关的文本需要翻译成中文：它是 https://dev59.com/uWw05IYBdhLWcg3wpTUV 的近复制，但没有被接受的答案。 - Daniel Vérité

Answer 1

对于UPDATE，这将更加简单，因为添加到更新中的其他行可见于RETURNING子句中：

• 仅使用SQL返回更新前列的值

但是对于INSERT目前来说是不可能的。手册：

该表达式可以使用由table_name命名的表的任何列名称

table_name是INSERT命令的目标。

您可以使用(数据修改) CTEs使其工作。假设title在查询中是唯一的，否则您需要进行更多操作：

WITH sel AS (
   SELECT id, title
   FROM   posts
   WHERE  id IN (1,2)   -- select rows to copy
   )
, ins AS (
   INSERT INTO posts (title)
   SELECT title FROM sel
   RETURNING id, title
 )
SELECT ins.id, sel.id AS from_id
FROM   ins
JOIN   sel USING (title);

如果每个查询中title不是唯一的（但至少每个表中id是唯一的）：

WITH sel AS (
   SELECT id, title, row_number() OVER (ORDER BY id) AS rn
   FROM   posts
   WHERE  id IN (1,2)   -- select rows to copy
   ORDER  BY id
   )
, ins AS (
   INSERT INTO posts (title)
   SELECT title FROM sel ORDER  BY id  -- ORDER redundant to be sure
   RETURNING id
 )
SELECT i.id, s.id AS from_id
FROM  (SELECT id, row_number() OVER (ORDER BY id) AS rn FROM ins) i
JOIN   sel s USING (rn);

这个第二个查询依赖于未记录的实现细节，即按提供的顺序插入行。它适用于所有当前版本的Postgres，并且可能不会出问题。

db<>fiddle 在这里
_{旧的sqlfiddle}

- Erwin Brandstetter

---

很好的答案。第二个查询中倒数第二行应该将列别名放在分区子句之外 FROM (SELECT id, row_number() OVER (ORDER BY id) AS rn FROM ins) i。 - salient

关于“查询依赖于未记录的实现细节，即行按提供的顺序插入”，@AdrianKlaver 指引我到了https://www.postgresql.org/message-id/1678721.1681566044%40sss.pgh.pa.us，Tom Lane在那里写道：“我们拒绝了INSERT必须保留传入元组顺序的想法”。 - Bergi

Answer 2

如果posts表的id列是serial类型，那么它会像这样生成：nextval('posts_id_seq'::regclass)。 你可以为每一行手动调用此函数。

with
sel as (
  SELECT id, title, nextval('posts_id_seq'::regclass) new_id
  FROM   posts
  WHERE  id IN (1,2)
),
ins as (
  INSERT INTO posts (id, title)
  SELECT new_id, title
  FROM sel
)
SELECT id, new_id
FROM sel

它可以处理任何数据，包括非唯一的title。

- Matveev Dmitriy

---

@ErwinBrandstetter，您对这种方法有什么看法？ - Manoharan

Answer 3

在我看来，最简单的解决方案是在表中添加一列，在该列中可以放置被克隆行的ID。

- murison

---

是的，这可能是最简单的方法，也适用于其他SQL数据库。但它会给数据库模式增加额外的复杂性。 - fengye87