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.
Solution
—————
1) use setInt or setLong to cast to the appropriate data type before send statement to PostgreSQL
2) use explicit casting like ::smallint
for example
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.
About Author
Sir, you are the ONE! I have followed many an articles by you and done many a things! So I attribute all my success to you!
Or, you can simply create CAST at database level:
CREATE FUNCTION pg_catalog.text(smallint) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS ‘SELECT textin(int2out($1));’;
CREATE CAST (smallint AS text) WITH FUNCTION pg_catalog.text(smallint) AS IMPLICIT;