Thread: BigDecimal
We're starting a Java project for our retail stores using Postgres,
and would like to store BigDecimal values for price, payment, etc.
because the data will be exported to an IBM iSeries server ("AS/400").
What is the correct Postgres data type to use? I would assume
"decimal"; when the docs say "User-specified precision" with a range
of "~8000 digits" does that mean the decimal point can fall anywhere
within those 8000 digits?
I was able to write a BigDecimal into a field of type "decimal" using
statement.executeQuery("INSERT INTO TEST VALUES(" + myBigDecimal + ")");
but when I try to read using ResultSet.getBigDecimal(), I get an
error message saying it's not implemented. We're using Postgres
7.0.3 on SuSE Linux. Should we upgrade to 7.1? I realize 7.2 just
came out, but we prefer to install from SuSE RPMs, which I don't
think are available yet. Or is there a later driver that implements
this, that we could use with 7.0 or 7.1?
--
____________________________________________________________
Glenn Holmer gholmer@weycogroup.com
Programmer/Analyst phone: 414.908.1809
Weyco Group, Inc. fax: 414.908.1601
Glenn,
The latest driver does appear to implement BigDecimal. There is no
reason not to use this driver, it is quite independent of the version of
postgres.
FWIW I use a long for money and store everything in pennies, this
alleviates most rounding issues
Dave
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Glenn Holmer
Sent: Monday, February 11, 2002 9:46 AM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] BigDecimal
We're starting a Java project for our retail stores using Postgres, and
would like to store BigDecimal values for price, payment, etc. because
the data will be exported to an IBM iSeries server ("AS/400"). What is
the correct Postgres data type to use? I would assume "decimal"; when
the docs say "User-specified precision" with a range of "~8000 digits"
does that mean the decimal point can fall anywhere within those 8000
digits?
I was able to write a BigDecimal into a field of type "decimal" using
statement.executeQuery("INSERT INTO TEST VALUES(" + myBigDecimal + ")");
but when I try to read using ResultSet.getBigDecimal(), I get an error
message saying it's not implemented. We're using Postgres 7.0.3 on SuSE
Linux. Should we upgrade to 7.1? I realize 7.2 just came out, but we
prefer to install from SuSE RPMs, which I don't think are available yet.
Or is there a later driver that implements this, that we could use with
7.0 or 7.1?
--
____________________________________________________________
Glenn Holmer gholmer@weycogroup.com
Programmer/Analyst phone: 414.908.1809
Weyco Group, Inc. fax: 414.908.1601
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Dave Cramer wrote: > Glenn, > > The latest driver does appear to implement BigDecimal. There is no > reason not to use this driver, it is quite independent of the version of > postgres. Thanks, it does work nicely with 7.0. > FWIW I use a long for money and store everything in pennies, this > alleviates most rounding issues We just wanted to use the closest type to the way the AS/400 stores it (EBCDIC packed & zoned decimal fields). -- ____________________________________________________________ Glenn Holmer gholmer@weycogroup.com Programmer/Analyst phone: 414.908.1809 Weyco Group, Inc. fax: 414.908.1601
Glenn Holmer <gholmer@weycogroup.com> writes:
> We're starting a Java project for our retail stores using Postgres,
> and would like to store BigDecimal values for price, payment, etc.
> because the data will be exported to an IBM iSeries server ("AS/400").
> What is the correct Postgres data type to use?
I'd say decimal a/k/a numeric.
> I would assume
> "decimal"; when the docs say "User-specified precision" with a range
> of "~8000 digits" does that mean the decimal point can fall anywhere
> within those 8000 digits?
Where did you find this remark about "8000 digits"? The actual
limitation imposed by the source code seems to be 1000 digits, although
I'm darned if I can see why it's not closer to 8000 ...
regards, tom lane
Tom Lane wrote: > Where did you find this remark about "8000 digits"? The actual > limitation imposed by the source code seems to be 1000 digits, although > I'm darned if I can see why it's not closer to 8000 ... In the 7.0 User's Guide, under 3. Data Types/Numeric Types. I see that it's different in the 7.2 online docs. -- ____________________________________________________________ Glenn Holmer gholmer@weycogroup.com Programmer/Analyst phone: 414.908.1809 Weyco Group, Inc. fax: 414.908.1601