SQL 如何使用复制命令将数据从一个表复制到 postgres 中的另一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31284514/
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
How do I copy data from one table to another in postgres using copy command
提问by Mohitd23
We use copy command to copy data of one table to a file outside database.
我们使用 copy 命令将一张表的数据复制到数据库外的文件中。
Is it possible to copy data of one table to another table using command.
是否可以使用命令将一个表的数据复制到另一个表。
If yes can anyone please share the query.
如果是，任何人都可以分享查询。
Or is there any better approach like we can use pg_dump or something like that.
或者有没有更好的方法，比如我们可以使用 pg_dump 或类似的东西。
Appreciate your help.
感谢你的帮助。
Regards, Mohit
问候， 莫希特
回答by Craig Ringer
You cannot easily do that, but there's also no need to do so.
你不能轻易做到这一点，但也没有必要这样做。
CREATE TABLE mycopy AS
SELECT * FROM mytable;
or
或者
CREATE TABLE mycopy (LIKE mytable INCLUDING ALL);
INSERT INTO mycopy
SELECT * FROM mytable;
If you need to select only some columns or reorder them, you can do this:
如果您只需要选择某些列或对它们重新排序，您可以这样做：
INSERT INTO mycopy(colA, colB)
SELECT col1, col2 FROM mytable;
You can also do a selective pg_dump and restore of just the target table.
您还可以执行选择性 pg_dump 并仅还原目标表。
回答by Manish Jain
Suppose there is already a table and you want to copy all records from this table to another table which is not currently present in the database then following query will do this task for you:
假设已经有一个表，并且您想将该表中的所有记录复制到另一个当前不在数据库中的表中，那么以下查询将为您完成此任务：
SELECT * into public."NewTable" FROM public."ExistingTable";
回答by Steve Irwin
If the columns are the same (names and datatypes) in both tables then you can use the following
如果两个表中的列相同（名称和数据类型），则可以使用以下内容
INSERT INTO receivingtable (SELECT * FROM sourcetable WHERE column1='parameter' AND column2='anotherparameter');
