Re: Mapping Java BigDecimal - Mailing list pgsql-jdbc

I would like to disagree with the statement that PostgreSQL numeric is a
real with rounding to the precision specified.

SELECT ('0.10000000000000000000000000000000'::numeric * '0.1'::numeric) -
'0.01'::numeric;
0.000000000000000000000000000000000

In Java its identical.
   java.math.BigDecimal test = new java.math.BigDecimal("0.1");
   java.math.BigDecimal test2 = new java.math.BigDecimal("0.1");
   test = test.setScale(32);
   test = test.multiply(test2).subtract(new java.math.BigDecimal("0.01"));
   System.out.println(test.toPlainString());
0.000000000000000000000000000000000

Where as with floating point calculations things are different.
SELECT ('0.1'::float8 * '0.1'::float8) - '0.01'::float8;
1.73472347597681e-18

As is with Java
   double test = 0.1;
   double test2 = 0.1;
   test = test * test2 - 0.01;
   System.out.println(test);
1.734723475976807E-18

You will note that numeric performs EXACT numeric calculations within the
precision specified. Real does a good job but its storage mechanism means
that some numbers cannot be exactly represented, for example '0.1', and
therefore the results are reflected by the calculations.

Its speed vs accuracy, numeric is slow at calculations where as real is
fast, one gives exact answers and one does not.

Numeric data for databases traditionally use binary coded decimal as the
storage mechanism, which means for every 2 digits of precision required, 1
byte of storage is needed. I do not know what PostgreSQL uses under the
bonnet, but given that the storage is variable, it would suggest that it is
something similar.
Real data type storage is fixed, either 4 or 8 bytes depending on precision
and range required.

Now for the Java side of things.
BigDecimal is the best fit for numeric. You can match the scale and
precision exactly as you would on the database and the formatting is as
expected.

Here are some of the main differences.

With PostgreSQL you cannot just set the scale, you must set the precision as
well as the scale. A little annoying.

PostgreSQL:
SELECT ('2.0'::numeric = '2.00'::numeric)::bool
't'
And just to be more exact, we will define two different scales.
SELECT ('2.0'::numeric(8,1) = '2.00'::numeric(8,2))::bool
't'

Compared to Java
System.out.println((new BigDecimal("2.0").equals(new BigDecimal("2.00"))));
false.
The scales must be identical to get a true result from the equals operator
in Java.

Another annoying issue with BigDecimal is the output formatting in a table.
Sun's code converts a BigDecimal to double and uses the floating point
number in the format string, which can give you odd behaviour and possibly
not what expected.

As always do you own formatting and you will get what you asked for.

I think that you should forget the Oracle data type and just understand that
PostgreSQL Numeric and Java BigDecimal are the best match for those data
types.

Donald

----- Original Message -----
From: "dmp" <danap@ttc-cmc.net>
To: <pgsql-jdbc@postgresql.org>
Sent: Tuesday, January 19, 2010 2:59 PM
Subject: Re: [JDBC] Mapping Java BigDecimal


> Yesterday I indirectly, but did not completely demonstrate
> the difference between the BigDecimal and Numeric data
> types in the databases. My example indicated the precision
> is carried and return in the default when no precision is given
> for Numeric, but more importantly:
>
> 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 ('0.056', '0.056');
> INSERT 0 1
> postgres=# SELECT SUM(x), SUM(y) FROM test;
>  sum  | sum
> -------+------
> 3.536 | 3.54
> (1 row)
>
> As indicated PostgreSQL kept the precision and rounded for
> the specified y column to the precision defined. The unspecified
> precision column x just kept on adding precision decimal places.
> If you chop either the x or y column decimal places you will
> get the whole Integer 3. The same process in Oracle for the summing
> with a BigDecimal data type I suspect most likely will return 4.
> The BigDecimal is a BigInteger rounded to the specified precision.
> Quite a difference creature than the Numeric(19, 2) in PostgreSQL,
> which is real with rounding to the precision specified, not to the
> nearest whole Integer.
>
> Perhaps this whole mapping question should be put over to the
> database forum for an appropriate answer to BigDecimal to what
> in PostgreSQL.
>
> danap.