postgresql版本将数据从一个表移动到另一个表

I'd like to move some data from one table to another (with a possibly different schema). Straightforward solution that comes into mind is -

我想将一些数据从一个表移动到另一个表（可能有不同的模式）。想到的直接解决方案是 -

start a transaction with serializable isolation level;
INSERT INTO dest_table SELECT data FROM orig_table,other-tables WHERE <condition>;
DELETE FROM orig_table USING other-tables WHERE <condition>;
COMMIT;

Now what if the amount of data is rather big, and the <condition> is expensive to compute? In PostgreSQL, a RULE or a stored procedure can be used to delete data on the fly, evaluating condition only once. Which solution is better? Are there other options?

现在如果数据量相当大并且计算成本高昂怎么办？在PostgreSQL中，RULE或存储过程可用于动态删除数据，仅评估条件一次。哪种解决方案更好？还有其他选择吗？

4 个解决方案

#1

[Expanding on dvv's answer]

[扩展dvv的答案]

You can move to an existing table as follows. For unmatched schema, you should specify columns.

您可以按如下方式移动到现有表。对于不匹配的架构，您应指定列。

WITH moved_rows AS (
    DELETE FROM <original_table> a
    USING <other_table> b
    WHERE <condition>
    RETURNING a.* -- or specify columns
)
INSERT INTO <existing_table> --specify columns if necessary
SELECT [DISTINCT] * FROM moved_rows;

But you want to move the data into a new table (not an existing one), the outer syntax is different:

但是您希望将数据移动到新表（而不是现有表）中，外部语法不同：

CREATE TABLE <new_table> AS
WITH moved_rows AS (
    DELETE FROM <original_table> a
    USING <other_table> b
    WHERE <condition>
    RETURNING a.* -- or specify columns
)
SELECT [DISTINCT] * FROM moved_rows;

#2

If the condition is so complicated that you don't want to execute it twice (which BTW sounds unlikely to me, but anyway), one possibility would be to ALTER TABLE ... ADD COLUMN on the original table to add a boolean field, and run an UPDATE on the table to set that field to true WHERE <condition>. Then your INSERT and DELETE commands can simply check this column for their WHERE clauses.

如果条件太复杂，你不想执行它两次（BTW听起来不太可能，但无论如何），一种可能性是在原始表上添加表...添加一个布尔字段，添加一个布尔字段，并在表上运行UPDATE以将该字段设置为true WHERE 。然后，您的INSERT和DELETE命令可以只检查此列的WHERE子句。

Don't forget to delete the column from both source and destination tables afterwards!

之后不要忘记从源表和目标表中删除该列！

Hmm, even less intrusive would be to create a new temporary table whose only purpose is to contain the PKs of records that you want to include. First INSERT to this table to "define" the set of rows to operate on, and then join with this table for the table-copying INSERT and DELETE. These joins will be fast since table PKs are indexed.

嗯，更不干扰的是创建一个新的临时表，其唯一目的是包含您想要包含的记录的PK。首先INSERT到此表以“定义”要操作的行集，然后与此表连接以进行表复制INSERT和DELETE。由于表PK被索引，因此这些连接将很快。

[EDIT] Scott Bailey's suggestion in the comments is obviously the right way to do this, wish I'd thought of it myself! Assuming all the original table's PK fields will be present in the destination table, there's no need for a temporary table -- just use the complex WHERE conditions to insert into the destination, then DELETE from the original table by joining to this table. I feel stupid for suggesting a separate table now! :)

[编辑]斯科特贝利在评论中的建议显然是正确的方法，希望我自己想到它！假设所有原始表的PK字段都将出现在目标表中，则不需要临时表 - 只需使用复杂的WHERE条件插入目标，然后通过连接到该表从原始表中删除。现在建议一个单独的桌子我觉得很蠢！ :)

#3

You can move data using SINGLE query in Postgres 9.1 See http://www.postgresql.org/docs/9.1/static/queries-with.html Section "Data-Modifying Statements in WITH"

您可以使用Postgres 9.1中的SINGLE查询移动数据。请参阅http://www.postgresql.org/docs/9.1/static/queries-with.html“在WITH中修改数据的数据”部分

#4

You might dump the table data to a file, then insert it to another table using COPY Usually COPY is faster than INSERT.

您可以将表数据转储到文件，然后使用COPY将其插入另一个表。通常COPY比INSERT快。

#1