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

Tsyklop
  • 369
  • 1
  • 9
  • 24
  • I tried one thing. And if pass `null` [there](https://gitlab.com/Tsyklop/jpa-test/-/blob/master/src/main/java/com/example/jpatest/service/UserManageServiceImpl.java#L33) I got error. Is there any workarounds? I want search all rows without timestamp filter or with timestamp filter. – Tsyklop Apr 17 '22 at 15:14

1 Answers1

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.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
  • Thanks for your answer. Read this post: https://github.com/spring-projects/spring-data-jpa/issues/2491#issuecomment-1102348149 – Tsyklop Apr 19 '22 at 09:19
  • 2
    IDK if this depends on the Postgresql version but you might use this expression: `CAST (:createdAtFrom AS TIMESTAMP)` (AS instead of TO) – Bolesław Denk Sep 28 '22 at 11:36