Re: Mapping Java BigDecimal

Craig Ringer wrote:
> Jakub Bednář wrote:
>
>> Hi All,
>>
>> We decide add support PostgreSQL database (now supporting only Oracle
>> database) to our product.
>>
>> In Oracle we mapping Java BigDecimal to number(19, 2), in PostgreSQL to
>> numeric(19, 2).
>>
>> If I store to "BigDecimal column" number without decimal, e.g. "3", than
>> Oracle JDBC driver return "3", but PostgreSQL JDBC driver return "3.00".
>>
>
> You're retrieving a number with two digits of precision, so it's giving
> that to you.
>
>
>> Is there some way (mapping, server setup, jdbc driver setup,...) how
>> reach return number without trailing zeroes on decimal position?
>>
>
> If you map the column in Pg as "numeric" without scale and precision
> then it'll store whatever scale and precision you give it. If you map it
> in Pg with a specified scale and precision, input values will be treated
> as being of that scale and precision. It sounds like Oracle instead
> treats those as *limits* for values and preserves the input scale and
> precision even if they're specified for the column type.
>
> I don't know whether Oracle or Pg are more "correct" here - you're
> giving Pg "3" so arguably it shouldn't assume "3.00" and should in fact
> return "3". OTOH, you've told it what the scale and precision are for
> the column, and inputs to the column should be presumed to fit that
> scale and precision.
>
> You should probably just strip the trailing zeroes for display when you
> format your numbers for the current locale, and retain the internal
> representation however it is. Alternately, store 'numeric' in Pg to
> retain the input scale and precision.
>
> regress=> create table test (x numeric, y numeric(19,2));
> CREATE TABLE
> regress=> insert into test VALUES ('3', '3');
> INSERT 0 1
> regress=> select * from test;
>  x |  y
> ---+------
>  3 | 3.00
> (1 row)
>
>
> ... and if you want, use a CHECK constraint to limit it. You could wrap
> that up in a domain type if you like. I tend to create a domain anyway
> for my NUMERIC types so I don't have to repeat the scale and precision
> all over the place, and so the name of the type more accurately reflects
> its use (eg currency types, etc).
>
> --
> Craig Ringer
>
Thanks Craig.

JB