I have a Java EE web application developed in an old version of Eclipse (Ganymede if I remember correctly). I recently migrated to Kubuntu 12.04 LTS and migrated the application to Eclipse Kepler (which I downloaded and installed from the Eclipse website). It is using Java Compliance Level 1.6 and the target container is Tomcat 6.

My problem is that I now receive the error:

org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying

when the application encounters a page with a certain Postgres query in it. I'm using the JSTL sql:query and sql:param tags to implement a prepared statement within the jsp page. I know this is considered bad practice, but I'm not the original author and this technique is used throughout the application.

The error occurs because of trying to assign a string to an integer in the sql:param tag. In the previous setup any casting happened transparently and there was no error. With the new setup I receive the error.

I read that more strict type casting was introduced with Postgres 8.3 which would cause this error, but I am using the Postgres 8.2 JDBC 4 jar file within my application so it should work. I am stumped. Perhaps someone has an idea?

I came across a workaround, to multiply the string by 1 before making the comparison:

http://dev-answers.blogspot.co.uk/2010/08/type-coercion-in-jstl-for-sqlparam.html

But this is a bit of a kludge and I would have many pages to modify. But it is academic because I should not be experiencing the problem anyway.

Thanks for reading. Any help greatly appreciated.

You might be using the PostgreSQL 8.2 JDBC driver, but it looks like you're using a newer PostgreSQL server version. Try:

SELECT version()

Betcha it's 8.3 or newer.

These queries aren't really right and should preferably just be fixed. If you must you can alter the system catalogs to allow the cast implicitly, but this should be a temporary workaround only, until you can fix the queries the application is sending. The workaround proposed in that blog post is horrible, but so is JSTL if it doesn't offer type casts in a heavily typed language. Personally I'd be more inclined to force an explicit coercion in the query, eg in the blog's example:

<sql:query var="examples" dataSource="${exampleDataSource}">
    select ExampleName as "name"
    from ExampleTable 
    where ExampleId = ?::integer
    order by ExampleName ASC
    <sql:param value="${param['ID']}"/>
</sql:query>

? :: integer is a type cast in the PostgreSQL shorthand syntax. You can write the SQL standard CAST(? AS integer) if you prefer.

See: