INSERT INTO ... FROM SELECT ...返回ID映射 [英] INSERT INTO ... FROM SELECT ... RETURNING id mappings

问题描述

我正在使用PostgreSQL 9.3。

I'm using PostgreSQL 9.3.

我想复制一些数据库记录。由于我为表格使用了自动递增的pk ID，因此我想将ID映射从生成的重复记录ID还原为原始ID。例如，假设我有一个表 posts ，其中有2条记录：

I want to duplicate some of the db records. Since I'm using an auto-increment pk id for the table, I want to get back the id mappings from the generated ids of duplicated records to the original ones. For example, say I have a table posts with 2 records in it:

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

使用SQL：

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

我希望看到这样的映射：

I expect to see mappings like:

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

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

Any idea on how to fill in the question marks above to make it work? Thanks a lot!

推荐答案

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

This would be simpler for UPDATE, where additional rows joined into the update are visible to the RETURNING clause:

• 仅使用SQL返回更新前的列值- PostgreSQL版本

• Return pre-UPDATE Column Values Using SQL Only - PostgreSQL Version

目前 不 可能相同插入。 每个文档：

表达式可以使用以 table_name

$ b命名的表的任何列名称$ b

表名是 INSERT 命令的目标。

您可以使用（修改数据的）CTE
假设 title 在每个查询中都是唯一的，否则您需要做更多的事情：

You can use (data-modifying) CTEs to get this to work.
Assuming title to be unique per query, else you need to do more:

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 是唯一的）：

If title is not unique per query (but at least id is unique per table):

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版本，并且可能不会中断。

This second query relies on the undocumented implementation detail that rows are inserted in the order provided. It works in all current versions of Postgres and is probably not going to break.

SQL小提琴。

这篇关于INSERT INTO ... FROM SELECT ...返回ID映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！