Postgres Sql `could not determine data type of parameter` by Hibernate

The PostgreSQL driver tries to figure out the type of the parameters to tell those parameters directly to the PostgreSQL Server. This is necessary that the PostgreSQL server is able to compare fields. In case of a java.sql.Timestamp the PostgreSQL driver is just not able to do it since there are two matching fields for PostgreSQL. On the one side, there is the timestamp and on the other side the timestamptz with timezone information. The result is, that the PostgreSQL driver will just match it to Oid.UNSPECIFIED. This behavior is most of the time not an issue since the PostgreSQL server is able to detect the type. There is a detailed description of this issue in the PostgreSQL driver class PgPreparedStatement.

So what you can do is force cast to timestamp/date type only when Postgres is not able to detect proper type (when you are checking for null).
So instead of

(:fromDate is null )

use

(cast(:fromDate as date) is null )

same for toDate

Leave a Comment