postgresql Postgres 在表之间复制数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22115457/
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
Postgres copy data between tables
提问by liv a
I've created table1with columns a,b,c,dwhich has data in it.
table2is basically the same as table1it has different columns order + additional column i.e. a,e,d,b,cwith no data.
我创建table1了a,b,c,d包含数据的列。
table2基本相同，因为table1它具有不同的列顺序+附加列，即a,e,d,b,c没有数据。
how can I copy the data from table1into table2note that column ais an idand i wish the number will stay the same.
我如何将数据复制table1到table2注意列a是一个id，我希望数字保持不变。
this is what I've already tried:
这是我已经尝试过的：
insert into table2 select (a,d,b,c) from table1
this resulted in column "a" is of type bigint but expression is of type record
这导致 column "a" is of type bigint but expression is of type record
insert into table2 (a,d,b,c) values(select a,d,b,c from table1)
didn't work either syntax error at or near "select"
也没有用 syntax error at or near "select"
insert into table2 (a,e,d,b,c) values(select a,NULL,d,b,c from table1)
got the error: INSERT has more target columns than expressions
得到错误： INSERT has more target columns than expressions
回答by Rob Di Marco
Specify the column names you are inserting, but do not use valueswhen defining the select.
指定您要插入的列名称，但values在定义选择时不要使用。
insert into table2(a,d,b,c) select a, d, b, c from table1
回答by Matthew Layne
You can copy data between tables in postgreSQL by using:
您可以使用以下命令在 postgreSQL 中的表之间复制数据：
INSERT INTO [Tablename]([columns]) SELECT [Columns] FROM [Table to copy form];
INSERT INTO [Tablename]([columns]) SELECT [Columns] FROM [Table to copy form];
Which in your case would look like:
在您的情况下，它看起来像：
INSERT INTO table2(a,b,c,d) SELECT a,b,c,d FROM table1;
INSERT INTO table2(a,b,c,d) SELECT a,b,c,d FROM table1;
You can also easily create an empty table with the same table structure so that copying to it is easy, by using the command:
您还可以使用以下命令轻松创建一个具有相同表结构的空表，以便轻松复制到它：
CREATE TABLE [New Table] AS [Old Table] WITH NO DATA;
CREATE TABLE [New Table] AS [Old Table] WITH NO DATA;
And then running the INSERTcommand from before.
然后运行INSERT之前的命令。
If you simply want an identical copy you can run:
CREATE TABLE [New Table] as [Old Table];
如果你只是想要一个相同的副本，你可以运行：
CREATE TABLE [New Table] as [Old Table];
You can read more about copying data between tables in this article which I wrote for dataschool: https://dataschool.com/learn/copying-data-between-tables
您可以在我为 dataschool 编写的这篇文章中阅读有关在表之间复制数据的更多信息：https://dataschool.com/learn/copying-data-between-tables
You can read more about the insert command in the documentation here: https://www.postgresql.org/docs/9.2/sql-insert.html
您可以在此处的文档中阅读有关插入命令的更多信息：https: //www.postgresql.org/docs/9.2/sql-insert.html
回答by lucasls
The problem with the first solution is actually the parentheses. If you do:
第一个解决方案的问题实际上是括号。如果你这样做：
insert into table2 select a,d,b,c from table1
it's going to work, given table2has the same number of columns in the right order.
它会起作用，因为table2在正确的顺序中有相同数量的列。
This happens because in Postgres if you do
发生这种情况是因为在 Postgres 中，如果你这样做
select (1, 2), 3
it means you are selecting one field of type record and values 1 and 2 inside of it and one field of type int with value 3.
这意味着您正在选择一个类型为 record 的字段，其中的值为 1 和 2，以及一个值为 3 的 int 类型的字段。
