PostgreSQL：ERROR：操作符不存在：integer =字符变化 [英] PostgreSQL: ERROR: operator does not exist: integer = character varying

问题描述

这里我想创建视图如下例所示：

Here i am trying to create view as shown below in 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 */
 ....;

注意：在sql server中执行相同的查询，但在postgreSQL中获得上述错误。

Note: The same query executed in sql server but getting the above error in postgreSQL.

推荐答案

我想它告诉你究竟发生了什么问题。您不能将整数与varchar进行比较。 PostgreSQL是严格的，不为你做任何魔法typecasting。我猜SQLServer自动执行类型转换（这是一件坏事）。

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).

如果你想比较这两个不同的野兽，你必须把一个使用铸造语法 :: 。

If you want to compare these two different beasts, you will have to cast one to the other using the casting syntax ::.

沿着这些线：

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 */
....;

请注意table1上的 varchar typecasting。 col4。

Notice the varchar typecasting on the table1.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, immutable function which casts the values on the column. But this too may prove suboptimal (but better than live casting).

这篇关于PostgreSQL：ERROR：操作符不存在：integer =字符变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！