Optional date parameter in PostgreSQL query from Java not working

user_2269439 0
Viewed 706

We are migrating some services from Oracle to PostgreSQL and we have our custom database access layer, based on JDBC. Almost all of our use cases are working, but our queries with optional parameters (where field = :parameter or :parameter is null) are not.

The original query in Oracle was:

select field
from table
where (reference = ?)
  and (date >= ? or ? is null)

And it worked, we tested in MySQL and it worked too. However, with PostgreSQL we get the following error: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $3

We're testing directly with the JDBC PostgreSQL library:

private static void testQuery() {
   try {
      String ref = "dummy";
      String sql = "select field From table where (reference = ?)";
      sql += " and (date >= ? or ? is null) ";
      PreparedStatement ps = getConnection().prepareStatement(sql);
      ps.setString(1, ref);
      ps.setNull(2, Types.NULL);
      ps.setNull(3, Types.NULL);
      ResultSet rs = ps.executeQuery();
      while (rs.next()) {
         System.out.print("Column 1 returned: ");
         System.out.println(rs.getString(1));
      }
      rs.close();
      ps.close();
   } catch (Exception ex) {
      ex.printStackTrace();
   }
}

We did test with the coalesce function and it worked, but the idea was to have pure SQL queries that could work in any database.

We are using PostgreSQL 12.4 and the dependency is:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.16</version>
</dependency>

We could get it working specifying

ps.setNull(2, Types.NULL);
ps.setNull(3, Types.VARCHAR);

It seems that it's mandatory to have the VARCHAR type specified when comparing a parameter to null. This doesn't help us a lot since it's the same parameter from the query perspective, so we can't put two types depending on if the condition is null is used in our database access layer.

Until now, whenever a parameter was null, we were using VARCHAR as default for the Oracle and MySQL access, but then PostgreSQL gives the following error for the second parameter (date):

org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone >= character varying

Any ideas?

Edit: if I'm not mistaken and haven't misread this thread, there's not really a solution besides the (ugly in my opinion) option to specify the type of the nullable parameter in the query: select * from my_table where ?::timestamp. It seems that even JPA has the same problem and has to do the type casting.

postgresql sql java
2 Answers

As Andreas pointed out in his second comment, the problem is that the JDBC implementation of PostgreSQL is more "refined" than others and wants to know explicitly which type is the parameter, even if the value passed is null. This is not the case of other implementations like Oracle or MySQL, they're both happy with Varchar as a default type when the value is null.

We had to other option than to change all our queries to use COALESCE instead.

Here's an old thread explaining the problem if anyone is interested, and a more recent discussion.

user_22694390

This seems a little late but for someone who might run into this issue. I've had this problem in the past where I needed to convert the date (Java Date) to string and then back to Date, it never worked when converted back no matter what formatting I used. Until I used the Calendar.

Calendar calendar = Calendar.getInstance()
calendar.set(Calendar.YEAR, 1999)
calendar.set(Calendar.DAY_OF_MONTH, 1)
calendar.set(Calendar.MONTH, 12)
java.sql.Date sqlDate = new java.sql.Date(calendar.getTime().getTime())

After doing what I had to do with the converted to string date, I converted it to integers and passed it to the calendar as above, you get the drill. P.S. I wish I could cite the source where I found this. If someone does, please share the link here and I'll make sure to cite.

user_136032880
Related