PostgreSQL：错误：运算符不存在：整数 = 字符变化
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23622993/
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
PostgreSQL: ERROR: operator does not exist: integer = character varying
提问by Meem
Here i am trying to create view as shown below in example:
在这里，我正在尝试创建如下示例所示的视图：
Example:
例子：
create view view1
as
select table1.col1,table2.col1,table3.col3
from table1
inner join
table2
inner join
table3
on
table1.col4 = table2.col5
/* Here col4 of table1 is of "integer" type and col5 of table2 is of type "varchar" */
/* ERROR: operator does not exist: integer = character varying */
....;
Note: The same query executed in sql server but getting the above error in postgreSQL.
注意：在 sql server 中执行相同的查询，但在 postgreSQL 中出现上述错误。
回答by Timusan
I think it is telling you exactly what is wrong. You cannot compare an integer with a varchar. PostgreSQL is strict and does not do any magic typecasting for you. I'm guessing SQLServer does typecasting automagically (which is a bad thing).
我认为它可以准确地告诉您出了什么问题。不能将整数与 varchar 进行比较。PostgreSQL 是严格的，不会为你做任何神奇的类型转换。我猜 SQLServer 会自动进行类型转换（这是一件坏事）。
If you want to compare these two different beasts, you will have to cast one to the other using the casting syntax ::.
如果您想比较这两种不同的野兽，您必须使用强制转换语法将一个强制转换为另一个::。
Something along these lines:
沿着这些路线的东西：
create view view1
as
select table1.col1,table2.col1,table3.col3
from table1
inner join
table2
inner join
table3
on
table1.col4::varchar = table2.col5
/* Here col4 of table1 is of "integer" type and col5 of table2 is of type "varchar" */
/* ERROR: operator does not exist: integer = character varying */
....;
Notice the varchartypecasting on the table1.col4.
注意varchartable1.col4 上的类型转换。
Also note that typecasting might possibly render your index on that column unusable and has a performance penalty, which is pretty bad. An even better solution would be to see if you can permanently change one of the two column types to match the other one. Literately change your database design.
另请注意，类型转换可能会使您在该列上的索引不可用，并且会降低性能，这非常糟糕。更好的解决方案是查看您是否可以永久更改两种列类型中的一种以匹配另一种。彻底改变你的数据库设计。
Or you could create a index on the casted values by using a custom, immutablefunction which casts the values on the column. But this too may prove suboptimal (but better than live casting).
或者，您可以使用自定义的、不可变的函数在列上转换值，从而在转换后的值上创建索引。但这也可能被证明是次优的（但比现场直播要好）。
