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 the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
2 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
2 Comment authors
Cvele_new_accountDON_RK Recent comment authors
newest oldest most voted
Cvele_new_account
Guest
Cvele_new_account

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;

DON_RK
Guest
DON_RK

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!