插入``从选择''返回ID映射

Question

我正在使用PostgreSQL 9.3。

我想复制一些数据库记录。由于我正在为表使用自动递增的pk
ID，因此我想将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}]

关于如何填写上面的问号以使其起作用的任何想法吗？非常感谢！

Answer 1

这对于来说会更简单UPDATE，在此RETURNING子句中可以看到加入到更新中的其他行：

• 仅使用SQL返回UPDATE之前的列值-PostgreSQL版本

目前 无法
使用INSERT。每个文档：

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

table_name 是INSERT命令的目标。

您可以使用（数据修改）CTE使其正常工作。
假设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版本中使用，并且可能不会中断。

SQL提琴。