How to do decimal precision in Spring Data Jpa @Query Annotation-CodePudding

I am trying to do the following query in the query annotation. I am using postgresSQL. However if I use "::numeric(10, 2)" in the query then I got syntax error at or near ":". But if I remove "::numeric(10, 2)" this, it works fine.

@Query(value = "SELECT AVG(feedback_rating)::numeric(10, 2) FROM app_customer_feedback WHERE "  
        "created_date >= to_timestamp(:dateFrom, 'yyyy-mm-dd hh24:mi:ss') AND "  
        "created_date < to_timestamp(:dateTo, 'yyyy-mm-dd hh24:mi:ss')", nativeQuery = true)
Double getAverageByDateRange(@Param("dateFrom") String dateFrom, @Param("dateTo") String dateTo);

Can anyone tell me how can I solve this problem? Thanks!

CodePudding user response：

Colons need to be escaped!

Solution 1: escape colons
Every : needs to be replaced by \\:

AVG(feedback_rating)\\:\\:numeric(10, 2)

@Query(value = "SELECT AVG(feedback_rating)\\:\\:numeric(10, 2) FROM app_customer_feedback WHERE "  
        "created_date >= to_timestamp(:dateFrom, 'yyyy-mm-dd hh24:mi:ss') AND "  
        "created_date < to_timestamp(:dateTo, 'yyyy-mm-dd hh24:mi:ss')", nativeQuery = true)
Double getAverageByDateRange(@Param("dateFrom") String dateFrom, @Param("dateTo") String dateTo);

Solution 2: double colons
Every : needs to be replaced by double colons ::

AVG(feedback_rating)::::numeric(10, 2)

@Query(value = "SELECT AVG(feedback_rating)::::numeric(10, 2) FROM app_customer_feedback WHERE "  
        "created_date >= to_timestamp(:dateFrom, 'yyyy-mm-dd hh24:mi:ss') AND "  
        "created_date < to_timestamp(:dateTo, 'yyyy-mm-dd hh24:mi:ss')", nativeQuery = true)
Double getAverageByDateRange(@Param("dateFrom") String dateFrom, @Param("dateTo") String dateTo);

Solution 3: use Cast instead of colons

cast(AVG(feedback_rating) as numeric(10, 2))

@Query(value = "SELECT cast(AVG(feedback_rating) as numeric(10, 2)) FROM app_customer_feedback WHERE "  
        "created_date >= to_timestamp(:dateFrom, 'yyyy-mm-dd hh24:mi:ss') AND "  
        "created_date < to_timestamp(:dateTo, 'yyyy-mm-dd hh24:mi:ss')", nativeQuery = true)
Double getAverageByDateRange(@Param("dateFrom") String dateFrom, @Param("dateTo") String dateTo);