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 9.3. But after i set up PostgreSQL 9.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.

 <property name="statementBuilder">
     <bean id="xx" class="net.mlw.vlh.adapter.jdbc.util.StandardStatementBuilder">
 <property name="setters">
   <map>
   <entry key="sourceId">
    <ref local="intSetter"/>
   </entry>
 
  </map>
 </property>
     </bean>
  </property>