PostgresSQL + Spring JPA: org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to timestamp without time zone

1. The value of 'null' cannot be distinguished by JPA, especially for the mapping of 'date, timestamp, timestamptz' types in PostgreSQL.
2. My solusion as follows:

    @Query(value = "select distinct p.id, p.name, p.certification_date from project p " +
            "where LOWER(p.name) LIKE CONCAT('%',LOWER(:searchKey),'%') " +
            "and (cast(cast(:startDate as text) as timestamp) is null or p.CERTIFICATION_DATE >= :startDate) " +
            "and (cast(cast(:endDate as text) as timestamp) is null or p.CERTIFICATION_DATE < :endDate)",  nativeQuery = true)
    public List<Object[]> searchProjects(@Param("searchKey") String searchKey, @Param("startDate") Date startDate, @Param("endDate") Date endDate);

class Employee {
 int id;
 Date birthDate;
}

List<Employee> findByBirthDateNotNullAndBirthDateLessThanEqualAndBirthDateGreaterThanEqual(Date startDate,Date endDate)

Add casting only when comparing to null and not when actual data comparison occurs.

@Query(value = "select distinct p.id, p.name, p.certification_date from project p WHERE (LOWER(p.name) LIKE CONCAT('%',LOWER(:searchKey),'%') and ((cast(:startDate as date) is null or p.CERTIFICATION_DATE >= :startDate) AND (cast(:endDate as date) is null or p.CERTIFICATION_DATE < :endDate ))",  nativeQuery = true)
public List<Object[]> searchProjects(@Param("searchKey") String searchKey, @Param("startDate") Date startDate, @Param("endDate") Date endDate);

I had the same problem as you when my query had a timestamp filter, and this is how I solved it. I think this problem occurs because when JPA converts variables with ':' in front to native query, they will turn into '?' . This leads to variables having the same name in the code, but when sending sql to the DB, they will become '?' independent .So in the or condition both sides will be performed.

@Query(value = "select * " +
        "from qrcode.printer " +
        "where (cast(:name as text) is null or name like concat('%', cast(:name as text), '%')) " +
        "  and (cast(:email as text) is null or email like concat('%', cast(:email as text), '%')) " +
        "  and (cast(:createdAtFrom as text)  is null or created_at >= cast(cast(:createdAtFrom as text) as timestamp)) " +
        "  and (cast(:createdAtTo as text)  is null or created_at <= cast(cast(:createdAtTo as text) as timestamp)) " +
        "  and (cast(:updatedAtFrom as text)  is null or updated_at >= cast(cast(:updatedAtFrom as text) as timestamp)) " +
        "  and (cast(:updatedAtTo as text)  is null or updated_at <= cast(cast(:updatedAtTo as text) as timestamp)) " +
        "  and deleted = false " +
        "order by id desc", nativeQuery = true)
Page<Printer> findThenPaging(String name, String email, Date createdAtFrom, Date createdAtTo, Date updatedAtFrom, Date updatedAtTo, Pageable pageable);

With Spring you can also do it like this.

val rowParams = MapSqlParameterSource();
// `java.sql.Types.OTHER` indicates JDBC driver (and hence PostgreSQL) should handle the type conversion from a string to JSONB automatically.
rowParams.addValue("internalRawJson", dto.internalRawJson, Types.OTHER);