PostgreSQL – ERROR: operator does not exist: smallint = character varying (Solution)
Today i hit a weird error, actually my company is plan to migrate existing j2ee web application from PostgreSQL 8.2 to latest PostgreSQL 8.3. But after i set up PostgreSQL 8.3 properly, and start web application, i keep hiting following error
org.postgresql.util.PSQLException: ERROR: operator does not exist: smallint = character varying
It’s seem like data type mismatch error, funny is it working fine at PostgreSQL 8.2 but PostgreSQL 8.3? I googling few hours and find out, it’s probably has to do with the changes to implicit casting done in 8.3. It’s seem like PostgreSQL 8.3 put a lot effort to enchance and check data type.
This will delibrate a lot side effect, because we set string in valuelist to compare any coloums, and let PostgreSQL to do the casting automatically.
This is no longer support in PostgreSQL 8.3 (i guess). Here i provide two solution to solve above error.
1) use setInt or setLong to cast to the appropriate data type before send statement to PostgreSQL
2) use explicit casting like ::smallint
select * from score where scordid =?::smallint
If you are using valuelist like me, you need to wrap statementBuilder to override StandardStatamentBuilder to your appropriate data type.
Hope work to you.