Spring data jpa PostgreSql: operator does not exist: timestamp without time zone >= bytea

Question

Use Spring Boot 2.6.6, Spring data JPA, Hibernate 5.6.7.Final, PostgreSql Driver 42.3.3, PostgreSql Server 14.

I have query: SELECT u.* FROM "user" u WHERE ((:createdAtFrom = NULL OR :createdAtTo = NULL) OR (u.birthday BETWEEN :createdAtFrom AND :createdAtTo)).

But it not working.

I got error:

org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone >= bytea
 Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

I turned on hibernate debug for sql parameters and see next rows:

o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARBINARY] - [null]
o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [VARBINARY] - [null]
o.h.type.descriptor.sql.BasicBinder : binding parameter [3] as [VARBINARY] - [null]
o.h.type.descriptor.sql.BasicBinder : binding parameter [4] as [VARBINARY] - [null]

Why VARBINARY? I tried java.util.Date, java.time.LocalDateTime - same error. what wrong?

There is demo repo: https://gitlab.com/Tsyklop/jpa-test/-/tree/master

• Tsyklop: I tried one thing. And if pass null there I got error. Is there any workarounds? I want search all rows without timestamp filter or with timestamp filter.

Answer 1

Your statement seems to be missing a CAST so that Postgresql knows that the bind parameters are of the type of the columns they get compared to.

Also comparisons with NULL should always be made with IS NULL. See Why doesn't SQL support "= null" instead of "is null"?

So something like

SELECT u.* FROM "user" u 
WHERE ((:createdAtFrom IS NULL OR :createdAtTo IS NULL) 
OR (u.birthday BETWEEN 
 CAST (:createdAtFrom TO TIMESTAMP) 
 AND CAST (:createdAtTo TO TIMESTAMP))
)

should work.

• Tsyklop: Thanks for your answer. Read this post: github.com/spring-projects/spring-data-jpa/issues/…
• Bolesław Denk: IDK if this depends on the Postgresql version but you might use this expression: CAST (:createdAtFrom AS TIMESTAMP) (AS instead of TO)

Answer 2

In my case: Java (with Joda-Time), Hibernate, and PostgreSQL.

@Column
@Type(type="org.joda.time.contrib.hibernate.PersistentDateTime")
createdAtTo

To resolve it, simply use the solution provided in your entity class as described here: Joda-Time Hibernate User Guide. https://www.joda.org/joda-time-hibernate/userguide.html

Source code: GitHub - Joda-Time Hibernate https://github.com/JodaOrg/joda-time-hibernate Maven: Joda-Time Hibernate on Maven Repository https://mvnrepository.com/artifact/joda-time/joda-time-hibernate

• Yunnosch: Please, instead of your different case, please provide a solution to the case described in the question at the top of this page. Try for the explained form of an answer as per How to Answer.

Answer 3

In my case, whenever I need to use a variable that might be null, I use cast(:startTime as text)

Example : AND (cast(:startTime as text) IS NULL OR cb.startTime < :startTime)

• Yunnosch: Please, instead of your different case, please provide a solution to the case described in the question at the top of this page. Try for the explained form of an answer as per How to Answer.