有效地复制PostgreSQL表中的某些行 [英] Efficiently duplicate some rows in PostgreSQL table

问题描述

我有PostgreSQL 9数据库，该数据库使用自动递增的整数作为主键。我想复制一个表中的某些行（基于某些过滤条件），同时更改一个或两个值，即复制所有列值，但ID（自动生成）和可能另一列除外。 p>

但是，我也想从旧ID映射到新ID。有没有更好的方法呢？只是先查询要复制的行，然后一次插入一个新行？

本质上我想做这样的事情：

 插入my_table（col1，col2，col3）
选择col1，'new col2 value'，col3 
从my_table的old 
中，old.some_criteria ='something'
返回old.id，id； 
  

但是，此操作失败，并出现 ERROR：缺少表的FROM子句条目 ，我可以看到原因：Postgres必须先执行SELECT，然后将其插入，并且 RETURNING 子句只能访问新插入的子句

解决方案

RETURNING只能引用最后插入的行中的列。除非表中有一列既可以容纳它又包含新ID的字段，否则您不能以这种方式引用 OLD ID。

请尝试运行此方法，该方法应该可以将显示您可以通过RETURNING获得的所有可能的值：

 插入my_table（col1，col2，col3）
 SELECT col1，新col2值，col3 $ my $ table from my_table as old 
 WHERE old.some_criteria ='something'
返回*; 
  

它不会让您获得想要的行为，但应该更好地说明返回的设计原理

I have PostgreSQL 9 database that uses auto-incrementing integers as primary keys. I want to duplicate some of the rows in a table (based on some filter criteria), while changing one or two values, i.e. copy all column values, except for the ID (which is auto-generated) and possibly another column.

However, I also want to get the mapping from old to new IDs. Is there a better way to do it then just querying for the rows to copy first and then inserting new rows one at a time?

Essentially I want to do something like this:

INSERT INTO my_table (col1, col2, col3)
SELECT col1, 'new col2 value', col3
FROM my_table old
WHERE old.some_criteria = 'something'
RETURNING old.id, id;

However, this fails with ERROR: missing FROM-clause entry for table "old" and I can see why: Postgres must be doing the SELECT first and then inserting it and the RETURNING clauses only has access to the newly inserted row.

解决方案

RETURNING can only refer to the columns in the final, inserted row. You cannot refer to the "OLD" id this way unless there is a column in the table to hold both it and the new id.

Try running this which should work and will show all the possible values that you can get via RETURNING:

INSERT INTO my_table (col1, col2, col3)
    SELECT col1, 'new col2 value', col3
    FROM my_table AS old
    WHERE old.some_criteria = 'something'
RETURNING *;

It won't get you the behavior you want, but should illustrate better how RETURNING is designed to work.

这篇关于有效地复制PostgreSQL表中的某些行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！