2

I've got a double which I'm trying to save to a postgres numeric column.

The value I'm trying to save is 151.33160591125488, and I've verified that this is in fact the argument being received by Hibernates internals pre-insert.

However the value in the database post insert is 151.331605911255, ie it's been rounded to 12dp.

I know my column is of the right scale, as it's an unrestricted numeric column and the following...

update tbl set col=151.33160591125488

...has the desired effect.

So the culprit has to be either Hibernate or the postgres-JDBC driver.

Ideas?

EDIT:

org.hibernate.dialect.PostgreSQLDialect:

registerColumnType( Types.DOUBLE, "float8" );

and

select cast (151.33160591125488 as float8);

=151.331605911255

therefore the default behavior for double is incorrect, as it doesn't always save the double supplied to it.

Does anyone know how to get hibernate to use the column type "numeric" in postgres?

Matt Solnit
  • 32,152
  • 8
  • 53
  • 57
pstanton
  • 35,033
  • 24
  • 126
  • 168
  • You haven't really given enough details on the hibernate side. The mapping can specify precision and scale. for example: – Brian Oct 16 '09 at 01:24
  • Thanks brian, i'll try that however this would reduce the scalability of my app, all because hibernate is misbehaving. java double doesn't restrict, postgres doesn't restrict, why should hibernate? – pstanton Oct 16 '09 at 01:49
  • i tried with precision="30" scale="20" which should have been plenty, same result. so bad advice. – pstanton Oct 16 '09 at 01:55
  • 1
    FYI, the "postgres" tag was recently replaced with "postgresql". See http://meta.stackexchange.com/questions/25279/retag-request-postgres-postgresql for more info. I took the liberty of re-tagging this question. – Matt Solnit Oct 16 '09 at 04:55
  • 1
    just for your information, precision="12" scale="2" has no effect on inserts/updates, it is only used if hibernate also creates the database, so that it knows how to create the column. – Mauli Oct 17 '09 at 07:59

2 Answers2

3

Whenever precision matters use java.math.BigDecimal. This answer discusses this in great detail.

Community
  • 1
  • 1
n002213f
  • 7,805
  • 13
  • 69
  • 105
  • thanks, i'm sure this would work (and i will test it), however since i don't perform any arithmetic on this number the IEEE pitfalls don't apply. therefore double is perfectly appropriate for my needs. it still doesn't make sense for hibernate to inadvertently alter a piece of information i ask it to persist. – pstanton Oct 16 '09 at 10:31
  • from the JLS (http://java.sun.com/docs/books/jls/third_edition/html/typesValues.html) **The floating-point types are float, whose values include the 32-bit IEEE 754 floating-point numbers, and double, whose values include the 64-bit IEEE 754 floating-point numbers.** Therefore it does not matter whether you have done any calculations or not, you still have the pitfalls. – n002213f Oct 16 '09 at 12:14
  • how so? i have an exact double. i need it persisted. it doesn't change. if hibernate didn't cast it to float8 it would be persisted properly. in my experience you only have problems with IEEE 754 stuff when you do arithmetic on these numbers. prove me wrong. show an example. – pstanton Oct 16 '09 at 20:32
  • Testing with BigDecimal - it is not acceptable. it returns an invalid number. eg: System.out.println(new BigDecimal(-33.62112355233672)); = -33.62112355233671934229278122074902057647705078125. I am aware I can set the precision/scale, but the whole point is that i want a true floating point number. i don't want fixed scale. so to use bigdecimal i would have to interrogate my double first, figure out how may dp it uses, and set that on the BigDecimal? – pstanton Oct 16 '09 at 20:53
  • also, -33.62112355233672 is the equivalent of -33.62112355233672000000000000000000000000000000000 not -33.62112355233671934229278122074902057647705078125 – pstanton Oct 16 '09 at 20:54
  • using new BigDecimal(String.valueOf(-33.62112355233672))); returns desired result – pstanton Oct 16 '09 at 21:19
2

Java double is not really appropriate for precise math operations. Use BigDecimal instead. Hibernate supports it as one of basic types:

<property name="amount" type="big_decimal" />
ChssPly76
  • 99,456
  • 24
  • 206
  • 195
  • 1
    It doesn't matter whether you perform arithmetic or not. You **CANNOT** rely on `double` for **any** kind of precision. Besides, you've asked how to map to "NUMERIC" type - that's what "big_decimal" is mapped to. – ChssPly76 Oct 16 '09 at 15:06
  • show me one example of a double changing without arithmetic being performed. – pstanton Oct 16 '09 at 20:32