[Fwd: Re: Mapping Java BigDecimal]

> 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".
>
> Is there some way (mapping, server setup, jdbc driver setup,...) how
> reach return number without trailing zeroes on decimal position?
>
> I'm using JDBC4 PostgreSQL Driver (v. 8.4-701) and PostgreSQL v.
> 8.1.18 (default for CentoOS 5.3).
> Thank you all


Perhaps you should check the context of the use of this column in
your application. The designer looks like that precision which
the BigDecimal type provided was desired to be maintained by the
application. That BigDecimal if you check the docs, is a BigInteger
with a precision, scale, for rounding. The returned value is correct
as indicated by Oracle, because it really is an Integer, no decimal
places. The Numeric type in PostgreSQL is really not the same since
it is a real with specified precision and decimal places. If you
do choose Graig's recommendation, removing decimal places, you mind
as well limit perhaps the database storage for this column and define
as Numeric (19,2) which more clearly maintains the original intent
of the column. See below just numeric with no specification will
return decimal places if given.

postgres=# CREATE TEMP TABLE test (x numeric, y numeric(19,2));
CREATE TABLE
postgres=# INSERT INTO test VALUES ('3.48', '3.48');
INSERT 0 1
postgres=# INSERT INTO test VALUES ('3.53', '3.53');
INSERT 0 1
postgres=# SELECT SUM(x), SUM(y) FROM test;
sum  | sum
------+------
7.01 | 7.01
(1 row)

danap.