PostgreSQL:错误:运算符不存在:整数 = 字符变化 [英] 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 是严格的，不会为你做任何神奇的类型转换.我猜 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.col4 上的 varchar 类型转换.

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:错误:运算符不存在:整数 = 字符变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！