43

What PostgreSQL column type should I use to store a Java BigDecimal?

congusbongus
  • 13,359
  • 7
  • 71
  • 99
dajood
  • 3,758
  • 9
  • 46
  • 68

3 Answers3

34

See PostgreSQL datatypes - perhaps Numeric, which can act as an arbitrary precision type (this is a PostgreSQL extension).

...without any precision or scale creates a column in which numeric values of any precision and scale can be stored, up to the implementation limit on precision.

I am not entirely sure what "implementation limit on precision is", though. Have never tried really large numbers. If the limit is reached, then a fallback to text is possible. But, I suspect there will be significant other issues before then ;-) If you wish to store less, then specify a precision and/or scale to numeric.

Edit as sjr pointed out, the restriction is 1000 decimal digits of precision (from the same link):

The type numeric can store numbers with up to 1000 digits of precision [in currentl implementations] and perform calculations exactly. It is especially recommended for storing monetary amounts and other quantities where exactness is required...

If more precision is needed - despite having a much bigger problem at hand - then a numeric column will not be suitable (by itself). But this is really more of a very extreme "what if" and likely does not play a limiting role.

8

Just use the Java mappings for the common SQL data types. In this case you can use a NUMERIC or DECIMAL.

Sivaguru Srinivas
  • 53
  • 7
Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • This works in PostgreSQL because of the special support. However, SQL itself does not define an arbitrary precision numeric type. In SQL Server max precision is 38 digits. (Hopefully the BigDecimal value is guaranteed to be in that range though :-) –  Jan 10 '11 at 18:10
  • The question is about PostgreSQL, not about SQL Server. NUMERIC and DECIMAL have no limits in PostgreSQL: http://www.postgresql.org/docs/current/static/datatype-numeric.html – Frank Heikens Jan 10 '11 at 18:45
  • Even PostgreSQL, according to the documentation, has an upper limit of 1000 digits of precision. I have never had to use numbers anywhere near this large so I can not comment otherwise. (Java's BigDecimal has a *much* larger limit.) The point is, that the space of numeric is *smaller* and even though I know of no practical reason to exceed it, the fact should not be left unmentioned. –  Jan 10 '11 at 20:05
  • @pst: the NUMERIC datatype is a SQL standard –  Jan 10 '11 at 22:28
  • I would love to see a generic solution to this. Currently I am storing as a string but thats not very useful. – Jus12 Oct 23 '14 at 11:57
1

I'd use decimal adding my own scale and precision:

t.decimal "price", precision: 12, scale: 4, default: "0.0"
Anton Semenichenko
  • 354
  • 5
  • 14