Re: Mapping Java BigDecimal

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