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

Issue Analytics

State:
Created a year ago
Comments:18 (7 by maintainers)

Top GitHub Comments

1reaction

akakyicommented, May 31, 2022

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>

0reactions

vitr1988commented, Nov 1, 2022

Maybe this information https://blog.mimacom.com/java-8-dates-with-postgresql/ would be useful for solving your problem. As for me supposed solution works well.

Top Results From Across the Web

operator does not exist: timestamp without time zone >= bytea

Final, PostgreSql Driver 42.3.3, PostgreSql Server 14. I have query: SELECT u.* FROM "user" u WHERE ((:createdAtFrom = NULL OR :createdAtTo = ...

operator does not exist: timestamp without time zone + integer

To: pgsql-general(at)lists(dot)postgresql(dot)org. Subject: Re: ERROR: operator does not exist: timestamp without time zone + integer.

Spring data jpa PostgreSql: operator does not exist

org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp without time zone >= bytea Hint: No operator matches the given name and ...

ERROR: operator does not exist: timestamp without time zone ...

1 Answer · Edit the underlying base view and change the relevant date field to Field Type 'date' instead of 'timestamp'. · You...

What is a valid use case for using TIMESTAMP WITHOUT ...

It is considered valid for some to use timestamp WITHOUT time zone in situations where (1) everything is in the same timezone or...