postgresql Postgres 查询错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1686546/
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 query error
提问by Elitmiar
I have a query in postgres
我在 postgres 中有一个查询
insert into c_d (select * from cd where ak = '22019763');
And I get the following error
我收到以下错误
ERROR: column "region" is of type integer but expression is of type character varying
HINT: You will need to rewrite or cast the expression.
回答by Ed Carrel
An INSERT INTO table1 SELECT * FROM table2depends entirely on orderof the columns, which is part of the table definition. It will line each column of table1 up with the column of table2 with the same ordervalue, regardless of names.
AnINSERT INTO table1 SELECT * FROM table2完全取决于列的顺序，这是表定义的一部分。无论名称如何，它都会将 table1 的每一列与具有相同顺序值的 table2 列对齐。
The problem you have here is whatever column from cd with the same order value as c_d of the table "region" has an incompatible type, and an implicit typecast is not available to clear the confusion.
您在这里遇到的问题是 cd 中与表“区域”的 c_d 具有相同顺序值的任何列都具有不兼容的类型，并且无法使用隐式类型转换来清除混淆。
INSERT INTO SELECT *statements are stylistically bad form unless the two tables are defined, and will forever be defined, exactlythe same way. All it takes is for a single extra column to get added to cd, and you'll start getting errors about extraneous extra columns.
INSERT INTO SELECT *除非这两个表已定义，并且将永远以完全相同的方式定义，否则语句在风格上是糟糕的形式。只需将一个额外的列添加到 cd，您就会开始收到有关无关额外列的错误。
If it is at all possible, what I would suggest is explicitly calling out the columns within the SELECTstatement. You can call a function to change type within each of the column references (or you could define a new type cast to do this implicitly -- see CREATE CAST), and you can use ASto set the column label to match that of your target column.
如果可能的话，我的建议是明确调用SELECT语句中的列。您可以调用一个函数来更改每个列引用中的类型（或者您可以定义一个新的类型转换来隐式地执行此操作——请参阅 参考资料CREATE CAST），并且您可以使用它AS来设置列标签以匹配目标列的标签。
If you can't do this for some reason, indicate that in your question.
如果由于某种原因您不能这样做，请在您的问题中指出。
回答by Amanda Nyren
Check out the PostgreSQL insert documentation.The syntax is:
查看 PostgreSQL插入文档。语法是：
INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }
which here would look something like:
这里看起来像：
INSERT INTO c_d (column1, column2...) select * from cd where ak = '22019763'
This is the syntax you want to use when inserting values from one table to another where the column types and order are not exactly the same.
这是将值从一个表插入到另一个列类型和顺序不完全相同的表时要使用的语法。
