From: | Jakub Bednář <jakub(dot)bednar(at)b2bcentrum(dot)cz> |
---|---|
To: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Mapping Java BigDecimal |
Date: | 2010-01-18 08:56:55 |
Message-ID: | 4B542257.1040505@b2bcentrum.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Brad Milne | 2010-01-18 09:04:29 | 8.3 build 605 downloads broken? |
Previous Message | Craig Ringer | 2010-01-18 08:30:56 | Re: Mapping Java BigDecimal |