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.

1
2
3
4
5
6
7
8
9
10
11
12

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

Hope work to you.