Thread: BigDecimal

BigDecimal

From
Glenn Holmer
Date:
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


Re: BigDecimal

From
"Dave Cramer"
Date:
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



Re: BigDecimal

From
Glenn Holmer
Date:
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


Re: BigDecimal

From
Tom Lane
Date:
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

Re: BigDecimal

From
Glenn Holmer
Date:
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