Postgres Sql `could not determine data type of parameter` by Hibernate

Question

I have JpaRepository:

@Repository
public interface CardReaderRepository extends JpaRepository<CardReaderEntity, Integer > {


}

when i execute query in this repo same as this:

@Query(
    value = "SELECT new ir.server.component.panel.response." +
            "InvalidCardReaderDataDigestResponse(" +
            "    cr.driverNumber, cr.exploiterCode, cr.lineCode, " +
            "    cr.transactionDate, cr.offLoadingDate, " +
            "    cr.cardReaderNumber, " +
            "    sum(cr.count) , sum(cr.remind) " +
            ") " +
            "FROM CardReaderEntity cr " +
            "WHERE cr.company.id = :companyId " +
            "AND cr.status.id in :statusIds " +
            "AND cr.deleted = false " +
            "AND  (:fromDate is null or cr.offLoadingDate >= :fromDate ) " +
            "AND  (:toDate   is null or cr.offLoadingDate <= :toDate   ) " +
            "group by cr.driverNumber, cr.exploiterCode, cr.lineCode, cr.transactionDate, cr.offLoadingDate, cr.cardReaderNumber"
)
Page<InvalidCardReaderDataDigestResponse> findAllInvalidCardReaderDataDigestByCompanyIdAndStatusIdIn(
        @Param( "companyId" )   int companyId,
        @Param( "fromDate" )    Date fromDate,
        @Param( "toDate" )      Date toDate,
        @Param( "statusIds" )   List<Integer> statusIds,
        Pageable pageable
);

error is :

org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $3
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433) ~[postgresql-42.2.2.jar:42.2.2]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178) ~[postgresql-42.2.2.jar:42.2.2]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306) ~[postgresql-42.2.2.jar:42.2.2]

but when a change query to:

@Query(
    value = "SELECT new ir.server.component.panel.response." +
            "InvalidCardReaderDataDigestResponse(" +
            "    cr.driverNumber, cr.exploiterCode, cr.lineCode, " +
            "    cr.transactionDate, cr.offLoadingDate, " +
            "    cr.cardReaderNumber, " +
            "    sum(cr.count) , sum(cr.remind) " +
            ") " +
            "FROM CardReaderEntity cr " +
            "WHERE cr.company.id = :companyId " +
            "AND cr.status.id in :statusIds " +
            "AND cr.deleted = false " +
            "AND  (:fromDate is null ) " +
            "AND  (:toDate   is null ) " +
            "group by cr.driverNumber, cr.exploiterCode, cr.lineCode, cr.transactionDate, cr.offLoadingDate, cr.cardReaderNumber"
)
Page<InvalidCardReaderDataDigestResponse> findAllInvalidCardReaderDataDigestByCompanyIdAndStatusIdIn(
        @Param( "companyId" )   int companyId,
        @Param( "fromDate" )    Date fromDate,
        @Param( "toDate" )      Date toDate,
        @Param( "statusIds" )   List<Integer> statusIds,
        Pageable pageable
);

this work without error but a want run with fromDate and toDate

note:

offLoadingDate in CardReaderEntity:

@Basic
@Temporal( TemporalType.TIMESTAMP )
public Date getOffLoadingDate() {
    return offLoadingDate;
}
public void setOffLoadingDate(Date offLoadingDate) {
    this.offLoadingDate = offLoadingDate;
}

all of Date import in my java files is java.util.Date.

Can you share what is the format of the date parameter? Is it YYYY/MM/DD? And which one is the format on the database? — Carlos Alves Jorge
– Carlos Alves Jorge, Commented May 11, 2019 at 16:56
@CarlosAlvesJorge i've explained that in my question , that is TIMSTAMP format. — Morteza Jalambadani
– Morteza Jalambadani, Commented May 12, 2019 at 5:41
If you want to avoid nullability problems with Postgres, you have to drop the annotation, and implement the function on your own doing this part manually stackoverflow.com/a/53649111/3841161 — coladict
– coladict, Commented May 13, 2019 at 7:10

PowR · Accepted Answer · 2020-10-06 09:53:56Z

77

The PostgreSQL driver tries to figure out the type of the parameters to tell those parameters directly to the PostgreSQL Server. This is necessary that the PostgreSQL server is able to compare fields. In case of a java.sql.Timestamp the PostgreSQL driver is just not able to do it since there are two matching fields for PostgreSQL. On the one side, there is the timestamp and on the other side the timestamptz with timezone information. The result is, that the PostgreSQL driver will just match it to Oid.UNSPECIFIED. This behavior is most of the time not an issue since the PostgreSQL server is able to detect the type. There is a detailed description of this issue in the PostgreSQL driver class PgPreparedStatement.

So what you can do is force cast to timestamp/date type only when Postgres is not able to detect proper type (when you are checking for null). So instead of

(:fromDate is null )

use

(cast(:fromDate as date) is null )

same for toDate

answered Oct 6, 2020 at 9:53

PowR

1,20011 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Payal Over a year ago

hey @PowR, Im getting the same error but in my case it is Like query title ILIKE %:filter% Is there any other solution/Syntax for this

PowR Over a year ago

I haven't used postgres in a long time, but what I would try first: I guess you need to use apostrophes, but then maybe you can not pass variable there: title ILIKE '%:filter%' or if that does not help then pass the % signs to the variable in the "backend" side, so what I mean your :filter value should be "%Somthing%" and so the postgres ilike would look simply like: title ILIKE :filter

Sergey Zolotarev May 24 at 16:00

I wonder why does it have trouble with LocalDate too. LocalDate is explicitly time zone oblivious

dcalap · Accepted Answer · 2023-02-06 23:56:35Z

30

By my experience, you must assign a type to the outer value before you can ask PostgreSQL to check whether it is null or not. For example, this code won't work for PostgreSQL:

SELECT * FROM table WHERE $1 IS NULL OR column = $1;

And what would work is:

SELECT * FROM table WHERE $1::text IS NULL OR column = $1::text;

You can try applying the correct type to your variables to see if it works out. In your example, you should try

@Query(
    value = "SELECT new ir.server.component.panel.response." +
            "InvalidCardReaderDataDigestResponse(" +
            "    cr.driverNumber, cr.exploiterCode, cr.lineCode, " +
            "    cr.transactionDate, cr.offLoadingDate, " +
            "    cr.cardReaderNumber, " +
            "    sum(cr.count) , sum(cr.remind) " +
            ") " +
            "FROM CardReaderEntity cr " +
            "WHERE cr.company.id = :companyId " +
            "AND cr.status.id in :statusIds " +
            "AND cr.deleted = false " +
            "AND  (:fromDate::timestamp is null or cr.offLoadingDate >= :fromDate::timestamp ) " +
            "AND  (:toDate::timestamp   is null or cr.offLoadingDate <= :toDate::timestamp   ) " +
            "group by cr.driverNumber, cr.exploiterCode, cr.lineCode, cr.transactionDate, cr.offLoadingDate, cr.cardReaderNumber"
)

In fact, PostgreSQL won't ask for types in most of cases, checking null value is one of the special cases where type becomes necessary.

edited Feb 6, 2023 at 23:56

dcalap

1,0723 gold badges15 silver badges39 bronze badges

answered Nov 30, 2020 at 3:46

Terry Windwalker

1,9695 gold badges29 silver badges58 bronze badges

2 Comments

Dávid Leblay Over a year ago

The version @PowR gave works fine, but this syntax doesn't seem to. I get a QuerySyntaxException: unexpected token I've only tried the :fromDate::timestamp version, however. It might still work with $3::timestamp.

Terry Windwalker Over a year ago

@DávidLeblay Yep, using $x is always a better idea to avoid the error.

dev_2014 · Accepted Answer · 2022-08-09 14:58:56Z

8

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

    @Query(
    value = "SELECT new ir.server.component.panel.response." +
            "InvalidCardReaderDataDigestResponse(" +
            "    cr.driverNumber, cr.exploiterCode, cr.lineCode, " +
            "    cr.transactionDate, cr.offLoadingDate, " +
            "    cr.cardReaderNumber, " +
            "    sum(cr.count) , sum(cr.remind) " +
            ") " +
            "FROM CardReaderEntity cr " +
            "WHERE cr.company.id = :companyId " +
            "AND cr.status.id in :statusIds " +
            "AND cr.deleted = false " +
            "AND  (cast(:fromDate as timestamp) is null or cr.offLoadingDate >= :fromDate ) " +
            "AND  (cast(:toDate as timestamp)   is null or cr.offLoadingDate <= :toDate   ) " +
            "group by cr.driverNumber, cr.exploiterCode, cr.lineCode, cr.transactionDate, cr.offLoadingDate, cr.cardReaderNumber"
)
Page<InvalidCardReaderDataDigestResponse> findAllInvalidCardReaderDataDigestByCompanyIdAndStatusIdIn(
        @Param( "companyId" )   int companyId,
        @Param( "fromDate" )    Date fromDate,
        @Param( "toDate" )      Date toDate,
        @Param( "statusIds" )   List<Integer> statusIds,
        Pageable pageable
);

edited Aug 9, 2022 at 14:58

answered Aug 9, 2022 at 14:52

dev_2014

3814 silver badges6 bronze badges

3 Comments

Morteza Jalambadani Over a year ago

this section hase bug, cast(:fromDate as timestamp), if fromDate is null casting have issue, i solved my problem by query with NamedJdbcTemplate

dev_2014 Over a year ago

which version of postgres are you using? For me it worked on postgres 12.1.0

Morteza Jalambadani Over a year ago

when i asked question, the version of my postgres was 9.6

Collectives™ on Stack Overflow

Postgres Sql `could not determine data type of parameter` by Hibernate

3 Answers 3

3 Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related