SQL 将数据从一张表移动到另一张表，postgresql 版
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2974057/
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
move data from one table to another, postgresql edition
提问by IggShaman
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?
现在如果数据量相当大，<condition>计算成本高怎么办？在 PostgreSQL 中，规则或存储过程可用于动态删除数据，条件仅评估一次。哪个解决方案更好？还有其他选择吗？
回答by ADTC
[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 newtable (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;
回答by j_random_hacker
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 COLUMNon the original table to add a boolean field, and run an UPDATEon the table to set that field to true WHERE <condition>. Then your INSERTand DELETEcommands can simply check this column for their WHEREclauses.
如果条件太复杂以至于你不想执行它两次（顺便说一句，这对我来说不太可能，但无论如何），一种可能性是ALTER TABLE ... ADD COLUMN在原始表上添加一个布尔字段，然后UPDATE在表上运行将该字段设置为 true WHERE <condition>。然后您的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 INSERTto this table to "define" the set of rows to operate on, and then join with this table for the table-copying INSERTand 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 WHEREconditions to insert into the destination, then DELETEfrom the original table by joining to this table. I feel stupid for suggesting a separate table now! :)
[编辑]Scott Bailey 在评论中的建议显然是正确的方法，希望我自己能想到！假设所有原始表的 PK 字段都将出现在目标表中，则不需要临时表——只需使用复杂WHERE条件插入目标，然后DELETE通过连接到该表从原始表中插入。我现在为建议单独的表格而感到愚蠢！:)
回答by dvv
You can move data using SINGLE query in Postgres 9.1 See http://www.postgresql.org/docs/9.1/static/queries-with.htmlSection "Data-Modifying Statements in WITH"
您可以在 Postgres 9.1 中使用 SINGLE 查询移动数据，请参阅http://www.postgresql.org/docs/9.1/static/queries-with.html部分“WITH 中的数据修改语句”
回答by pcent
You might dump the table data to a file, then insert it to another table using COPYUsually COPYis faster than INSERT.
您可以将表数据转储到文件中，然后使用COPY通常COPY比INSERT.
