高效复制 PostgreSQL 表中的某些行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7115941/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Efficiently duplicate some rows in PostgreSQL table
提问by EMP
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.
我有使用自动递增整数作为主键的 PostgreSQL 9 数据库。我想复制表中的某些行（基于某些过滤条件），同时更改一个或两个值，即复制所有列值，除了 ID（自动生成）和可能的另一列。
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?
但是，我也想获取从旧 ID 到新 ID 的映射。有没有更好的方法来做到这一点，然后只查询要先复制的行，然后一次插入一个新行？
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 RETURNINGclauses only has access to the newly inserted row.
但是，这失败了ERROR: missing FROM-clause entry for table "old"，我可以理解为什么：Postgres 必须先执行 SELECT 然后插入它，并且RETURNING子句只能访问新插入的行。
回答by Matthew Wood
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.
RETURNING 只能引用最后插入的行中的列。您不能以这种方式引用“旧”ID，除非表中有一列同时包含它和新 ID。
Try running this which should work and will show all the possible values that you can get via RETURNING:
尝试运行它，它应该可以工作，并将显示您可以通过 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.
它不会让你得到你想要的行为，但应该更好地说明返回是如何设计的。
回答by pjhooker
Good! I test this code, but I change
this (FROM my_table AS old) in (FROM my_table) and
this (WHERE old.some_criteria = 'something') in (WHERE some_criteria = 'something')
好的！我测试了这段代码，但是我FROM my_table AS old在 ( FROM my_table) 中更改了 ( )，在 ( ) 中更改WHERE old.some_criteria = 'something'了 ( WHERE some_criteria = 'something')
This is the final code that I use
这是我使用的最终代码
INSERT INTO my_table (col1, col2, col3)
SELECT col1, 'new col2 value', col3
FROM my_table AS old
WHERE some_criteria = 'something'
RETURNING *;
Thanks!
谢谢！
回答by Erwin Brandstetter
This can be done with the help of data-modifiying CTEs (Postgres 9.1+):
这可以在数据修改 CTE（Postgres 9.1+）的帮助下完成：
WITH sel AS (
SELECT id, col1, col3
, row_number() OVER (ORDER BY id) AS rn -- order any way you like
FROM my_table
WHERE some_criteria = 'something'
ORDER BY id -- match order or row_number()
)
, ins AS (
INSERT INTO my_table (col1, col2, col3)
SELECT col1, 'new col2 value', col3
FROM sel
ORDER BY id -- redundant to be sure
RETURNING id
)
SELECT s.id AS old_id, i.id AS new_id
FROM (SELECT id, row_number() OVER (ORDER BY id) AS rn FROM ins) i
JOIN sel s USING (rn);
SQL Fiddledemonstration.
SQL Fiddle演示。
This relies on the undocumented implementation detail that rows from a SELECTare inserted in the order provided (and returned in the order provided). It works in all current versions of Postgres and is not going to break. Related:
这依赖于未记录的实现细节，即 aSELECT中的行按提供的顺序插入（并按提供的顺序返回）。它适用于所有当前版本的 Postgres，并且不会中断。有关的：
Window functions are not allowed in the RETURNINGclause, so I apply row_number()in another subquery.
RETURNING子句中不允许使用窗口函数，所以我row_number()在另一个子查询中应用。
More explanation in this related later answer:
在这个相关的稍后答案中有更多解释：
回答by Brian Keith
DROP TABLE IF EXISTS tmptable;
CREATE TEMPORARY TABLE tmptable as SELECT * FROM products WHERE id = 100;
UPDATE tmptable SET id = sbq.id from (select max(id)+1 as id from products) as sbq;
INSERT INTO products (SELECT * FROM tmptable);
DROP TABLE IF EXISTS tmptable;
add another update before the insert to modify another field
在插入之前添加另一个更新以修改另一个字段
UPDATE tmptable SET another = 'data';
回答by wildplasser
'old' is a reserved word, used by the rule rewrite system. [ I presume this query fragment is not part of a rule; in that case you would have phrased the question differently ]
'old' 是一个保留字，由规则重写系统使用。[我认为这个查询片段不是规则的一部分；在这种情况下，你会以不同的方式表达这个问题]
