PostgreSQL ERROR operator does not exist integer character varying

Question

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

User · Accepted Answer

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

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 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] PostgreSQL: ERROR: operator does not exist: integer = character varying

Examples related to postgresql

Examples related to casting

Examples related to integer

Examples related to varchar