有效地复制PostgreSQL表中的一些行

我有一个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子句只能访问新插入的行。

RETURNING只能引用最后插入行中的列。您不能以这种方式引用"OLD"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是如何设计的。

这可以在数据修改CTE(Postgres9.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()。

在这个相关的后面的答案中有更多的解释：

插入。。。从选择。。。返回id映射

好！我测试了这个代码，但我更改了这个(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 *;

谢谢！

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

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

相关内容

最新更新

热门标签：