高效地复制 PostgreSQL 表中的某些行

Question

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

但是，我还想获取从旧 ID 到新 ID 的映射。有没有更好的方法来做到这一点，然后只查询要复制的行，然后一次插入一个新行？

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

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;

但是，这失败了，错误：缺少表“旧”的 FROM 子句条目，我可以明白为什么：Postgres 必须首先执行 SELECT，然后插入它并且 RETURNING 子句只能访问新插入的行。

Answer 1

RETURNING 只能引用最后插入的行中的列。您不能以这种方式引用“旧”ID，除非表中有一列同时保存它和新 ID。

尝试运行它，它应该可以工作，并且会显示您可以通过 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 *;

它不会为您提供您想要的行为，但应该更好地说明 RETURNING 的设计工作方式。

Answer 2

这可以借助数据修改 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 Fiddle 演示。

这依赖于未记录的实现细节：SELECT 中的行按提供的顺序插入（并按提供的顺序返回）。它适用于所有当前版本的 Postgres，并且不会损坏。相关：

• Postgres 是否保留记录的插入顺序？

RETURNING 子句，因此我在另一个子查询中应用 row_number() 。

稍后相关答案中有更多解释：

• 插入.. .FROM SELECT ...返回 id 映射

Answer 3

好的！我测试了这段代码，但我改变了
(FROM my_table) 中的 (FROM my_table AS old) 和
(WHERE old.some_criteria = 'something') in (WHERE some_criteria = 'something')

这是我使用的最终代码

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

谢谢！

Answer 4

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;

在插入之前添加另一个更新以修改另一个字段

UPDATE tmptable SET another = 'data';

Answer 5

“old”是保留字，由规则重写系统使用。
[我认为这个查询片段不是规则的一部分；在这种情况下，你会以不同的方式表达这个问题]