sql - INSERT INTO ... FROM SELECT ... RETURNING 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 子句可见:

• Return pre-UPDATE column values using SQL only

目前 INSERT 不可能这样做。 The manual:

The expression can use any column names of the table named by table_name

table_name 是 INSERT 命令的目标。

您可以使用 (data-modifying) 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 here
<子>旧sqlfiddle