2

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

Improve this question
1
  • 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. Commented Apr 17, 2022 at 15:14

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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

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

Improve this answer

1 Comment

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

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)

Improve this answer

1 Comment

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.