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

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)) Native.

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 Stackoverflow: https://stackoverflow.com/questions/71902768/spring-boot-2-postgresql-operator-does-not-exist-timestamp-without-time-zone

About this issue

Original URL
State: closed
Created 2 years ago
Comments: 21 (8 by maintainers)

Most upvoted comments

After a 6 hours of useless brain suffering i manage to do this (just example):

@Query(
        value = "select " +
                "   t.* " +
                "from test t " +
                "where " +
                "   cast(cast(:endDate as text) as timestamp) is null or t.created_at <= cast(cast(:endDate as text) as timestamp)",
        nativeQuery = true
    )
    fun test(@Param("endDate") endDate: LocalDateTime?): List<Test>

akakyi on May 31, 2022