Casting double precision to numeric in postgresql

Hibernate ORM
1

Hi,

In the past few days I was trying to cast a double precision column to numeric using JPA Criteria.

When I tried using expression.as(BigDecimal.class), the generated SQL was cast(col AS numeric(19,2)) which has a scale of 2, which is something I don’t want as I want to be able to control the rounding myself. After debugging in Hibernate code I found out that this value of 2 is comming from Column.DEFAULT_SCALE, which is something I can’t override.

I, also, tried using criteriaBuilder.function('cast', BigDecimal.class, expression, cb.literal("numeric")), but the application was timed out with no response.

Hibernate version is 5.5.5.final

Note: I need this casting to use the round function which requires numeric instead of double precision, so any workaround to round a double precision to specific decimal places will also be acceptable.

Thank you in advance.

2

You will have to create a custom casting function in this case or if you just want to do rounding, you can also switch to a slightly more complex rounding emulation which was implemented in Hibernate 6: floor(col*1eN+0.5)/1eN, where N is the number of decimal places. For e.g. 3 decimal places, this would look like floor(col * 1000 + 0.5)/1000