Spring Data Rest: "Date is null" query throws an postgres exception

I've spend some time looking at this because I really want to compare "date is null" in querys and that's the result:

When you use the "cast(foo.date as date) is null", it works OK if the field is not null. If the field is null this exception is throwed:

org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to date

The solution is use coalesce:

coalesce(:date, null) is null

It works fine having or not data in the field tested.

My query example:

@Query("SELECT c "        + " FROM Entity c "        + " WHERE "        + "       and ( (coalesce(:dateFrom, null) is null and coalesce(:dateUntil, null) is null) "        + "             or ((coalesce(c.date, null) is not null) "        + "                 and ( "        + "                        ((coalesce(:dateFrom, null) is null and coalesce(:dateUntil, null) is not null) and c.date <= :dateUntil) "        + "                     or ((coalesce(:dateUntil, null) is null and coalesce(:dateFrom, null) is not null) and c.date >= :dateFrom)"        + "                     or (c.date between :dateFrom and :dateUntil)"        + "                 )"        + "             )"        + "       ) "

Hope it works for you!

I believe @Bonifacio is correct in that Postgres is not able to determine the type of your @Param("invoiceDate") parameter when performing the IS NULL test. I have queries similar to yours that behave as expected with an in memory H2 database, but fail with the Postgres integration tests.

I was able to get around this by casting the parameter to a date like so:

@Query(value = "SELECT ai FROM #{#entityName} ai WHERE "    + "(:invoiceNumber IS NULL OR ai.invoiceNumber LIKE :invoiceNumber) AND "    + "(cast(:invoiceDate as date) IS NULL OR ai.invoiceDate = :invoiceDate)"     )

I have had similar problems with the field of type LocalDateTime and field Timestamp in postgres 9.5.I solved it converting it like this:

    @Query("SELECT c "        + "FROM Contract c "        + "WHERE "        + "(:idContract IS NULL OR c.idContract = :idContract) AND "        + "(CAST(:dtCancelInitial AS java.time.LocalDateTime) IS NULL OR (c.dtCancel >= :dtCancelInitial AND c.dtCancel < :dtCancelFinal)) "        + "ORDER BY c.idContract")List<Contract> findContratConsultaCancelamento(@Param("idContract") Long idContract, @Param("dtCancelInitial") LocalDateTime dtCancelInitial, @Param("dtCancelFinal") LocalDateTime dtCancelFinal);