在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;

不过，这会出现错误：ERROR: missing FROM-clause entry for table "old"。我知道出错的原因是：Postgres 必须先执行 SELECT 然后再插入数据，而 RETURNING 子句只有对新插入的行有访问权限。

- EMP

---

你是否有想法在不命名我想复制的所有属性的情况下执行查询？我的表相当大（具有许多属性），输入所有这些并且不遗漏其中一个是很麻烦的... - Kamil Šrot

为什么有两个关于同一件事的问题？https://dev59.com/KF4b5IYBdhLWcg3wXAZ1 - murison

Answer 1

RETURNING 只能引用最终插入行中的列。除非表中有一个列用于保存旧id和新id，否则不能以这种方式引用“OLD” 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 的设计原理。

- Matthew Wood

---

谢谢 - 但我相信我已经理解了 RETURNING 的工作原理。 - EMP

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()。

更多解释请参见以下相关答案：

• INSERT INTO ... FROM SELECT ... RETURNING id mappings

- Erwin Brandstetter

Answer 3

很好！我测试了这段代码，但我将 (FROM my_table AS old) 改为 (FROM my_table)，并将 (WHERE old.some_criteria = 'something') 改为 (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 *;

谢谢！

- pjhooker

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';

- Brian Keith

---

如果id是整数而不是序列，则“UPDATE tmptable SET id =”才有效。 - DarkMukke

Answer 5

'old'是一个保留字，被重写规则系统使用。 [我假设这个查询片段不是规则的一部分；在那种情况下，您会用不同的方式来表达问题]

- wildplasser

---

是的，你说得对 - 我编造了这篇文章的查询，因为真实的查询更加复杂。 - EMP