[JDBC] Mapping Java BigDecimal
Jakub Bednář
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
----
Jakub Bednar
--
Sent via pgsql-jdbc mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Kris Jurka
On Mon, 18 Jan 2010, Jakub Bedn?? wrote:
> 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?
Nope. The behavior here is that of the server data type and there's
nothing the JDBC driver can do about it.
Kris Jurka
Craig Ringer
> 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
Jakub Bednář
JB
John R Pierce
> 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".
does a java BigDecimal number have a fixed 2 digit fraction precision
like that? If not, why not just define it as NUMERIC and let the
fractional part 'float' as assigned. ?
Jakub Bednář
> 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".
>
> does a java BigDecimal number have a fixed 2 digit fraction precision
> like that? If not, why not just define it as NUMERIC and let the
> fractional part 'float' as assigned. ?
>
>
>
>
to numeric without specify precision and scale.
Greg Stark
<cr...@postnewspapers.com.au> wrote:
> 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.
>
It doesn't really work that way. Whether you store "3" or "3.00" or
"3.0000" you're storing the same value in the numeric field.
The precision and scale you specify for Postgres is just the maximum
as well. But it's also used for the default formatting when converting
to text. If you retrieved the numeric in binary format it would be the
same regardless of the parameters.
In no case does postgres remember the precision of the input text. If
you don't specify a precision on the column it just prints as many as
necessar. That sounds like what you're looking for.
--
greg
Craig Ringer
> On Mon, Jan 18, 2010 at 8:30 AM, Craig Ringer
> <cr...@postnewspapers.com.au> wrote:
>> 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.
>>
> In no case does postgres remember the precision of the input text. If
> you don't specify a precision on the column it just prints as many as
> necessar. That sounds like what you're looking for.
Then I'm confused:
regress=> create table test (x numeric);
CREATE TABLE ^
regress=> insert into test (x) values ('3');
INSERT 0 1
regress=> insert into test (x) values ('3.0');
INSERT 0 1
regress=> insert into test (x) values ('3.00');
INSERT 0 1
regress=> insert into test (x) values ('3.000');
INSERT 0 1
regress=> select * from test;
x
-------
3
3.0
3.00
3.000
(4 rows)
--
Craig Ringer
Greg Stark
<cr...@postnewspapers.com.au> wrote:
> regress=> select * from test;
> x
> -------
> 3
> 3.0
> 3.00
> 3.000
> (4 rows)
>
oookay. I guess there is a case.
--
greg
dmp
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.
Thomas Kellerer
>> In no case does postgres remember the precision of the input text. If
>> you don't specify a precision on the column it just prints as many as
>> necessar. That sounds like what you're looking for.
>
> Then I'm confused:
>
> regress=> create table test (x numeric);
> CREATE TABLE ^
> regress=> insert into test (x) values ('3');
> INSERT 0 1
> regress=> insert into test (x) values ('3.0');
> INSERT 0 1
> regress=> insert into test (x) values ('3.00');
> INSERT 0 1
> regress=> insert into test (x) values ('3.000');
> INSERT 0 1
> regress=> select * from test;
> x
> -------
> 3
> 3.0
> 3.00
> 3.000
> (4 rows)
>
My first question: why does anyone pass a numeric value as a string ;)
But it does not seem to matter whether a real value (without quotes) or a string is used for the above example.
I also tested this with my JDBC/Java based SQL client, which allows me to configure the number of decimals shown.
It uses a DecimalFormat to format the numbers returned from the JDBC driver.
When using the above example and configuring the client to show 4 decimals (which results in a format mask of "0.#" and calling setMaxDigits() with a value of 4) I will get:
3
3.0000
3.0000
3.0000
Which is quite interesting as it seems that from the driver's point of view (or Java?) there is only a difference between no decimal digits or some decimal digits. It does not seem to get (or request) the information about how many decimals there were.
This seems to be done upon retrieving, because when inserting the above example data (again with or without quotes) from Java, the display in psql's display is the same as if the data had been inserted from psql.
Thomas
Kevin Grittner
>> INSERT 0 1
>> regress=> insert into test (x) values ('3.0');
>> INSERT 0 1
>> regress=> insert into test (x) values ('3.00');
>> INSERT 0 1
>> regress=> insert into test (x) values ('3.000');
>> INSERT 0 1
>> regress=> select * from test;
>> x
>> -------
>> 3
>> 3.0
>> 3.00
>> 3.000
>> (4 rows)
> My first question: why does anyone pass a numeric value as a
> string ;)
import java.math.BigDecimal;
class BigDecimalTests
{
public static void main(String[] args)
{
BigDecimal x;
x = new BigDecimal(3.000);
System.out.println(x.toPlainString());
x = new BigDecimal("3.000");
System.out.println(x.toPlainString());
x = new BigDecimal(1.01);
System.out.println(x.toPlainString());
x = new BigDecimal("1.01");
System.out.println(x.toPlainString());
}
}
For those without Java to play along at home, the result of
compiling and running this are:
3
3.000
1.0100000000000000088817841970012523233890533447265625
1.01
On top of that, in PostgreSQL '3.000' is *not* a character string,
as you are probably assuming. It is treated as type UNKNOWN until
it has to be resolved (similar to the treatment of a NULL literal),
so it can be interpreted as a literal of some other type.
-Kevin
Donald Fraser
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
dmp
> numeric is a real with rounding to the precision specified.
I concede my mis-statement.
> 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
Oracle NUMBER(19,2), (precision,scale) is just the same as
NUMERIC(19,2), but if do
Oracle NUMBER(19,2) ----> PostgreSQL Numeric, no precision yields the
desired result
as Jakub has figured out and keeps whatever precision input.
Donald Fraser
However I personally think that PostgreSQL is more correct.
In Oracle (excuse the format if this type-cast is not valid in Oracle - I've
never used Oracle)
SELECT '3'::number(6,2)
3
In PostgreSQL
SELECT '3'::numeric(6,2);
3.00
In Java
test = new BigDecimal("3").setScale(2);
System.out.println(test.toPlainString());
3.00
If you have set a scale in PostgreSQL you will always get that number of
decimal points and therefore your result from the database will always be
consistent and therefore predictable.
I would not want a database that returned data in any other way, just my
opinion of course.
Donald
----- Original Message -----
From: "dmp" <da...@ttc-cmc.net>
To: <pgsql...@postgresql.org>
Sent: Wednesday, January 20, 2010 4:27 AM
Subject: Re: [JDBC] Mapping Java BigDecimal