Обсуждение: JDBC: BigDecimal and Money confusion
Hi there folks, I searched the archives and haven't found anyone with this problem, but I'd be surprised if it's unique. We use BigDecimal for money values throughout our application. This is because our experience with using floats was that when you were adding up a large number of money values (e.g. to round an invoice to the nearest 5 cents), eventually we would have a problem with rounding errors causing our values to be out by cents. In order to talk more accurately with the database we converted everything to BigDecimals. We are now porting our application to Postgres (thanks guys +/or gals!!) and run into a problem that if we have a PreparedStatement and we attempt to use a BigDecimal in a money column, we get the exception, java.sql.SQLException: ERROR: Attribute 'amount' is of type 'money' but expression is of type 'float8'. It seems to me that a BigDecimal of scale 2 is a perfect fit for an attribute of type Money since it is perfectly accurate. I had a look at the JDBC driver's PreparedStatement class and worked out that it just uses strings to communicate with the back end. When I use psql I know I have to use quotes around money values in order to prevent them from being interpreted as floats. So, all I did was to modify the JDBC1 driver to call set(paramIndex, s) with a quoted string. I took this idea from the setString() method. This seems to work perfectly. While I haven't tried it yet, I think this would still work for float values. So, my questions are: (a) why couldn't I find anyone else having this problem? is it just me? (b) have I done the right thing? we have two days to port this so I didn't want to learn everything if I could avoid it (doh - we thought the hard part would be the ODBC bit)! and (c) is anyone interested in the patch, which I am happy to contribute? I would also patch the JDBC2 driver and I intend to look for an exception message bug that seems to want more java.text.format parameters than there are available. Anyway, I know I have jumped in with both feet here but we are very happy and impressed with Postgresql and I look forward to being able to contribute, if possible. I have quite a few questions about postgresql in general but I will wait until I am not such a newbie any more. So, apologies if I have done the wrong thing here. (As an aside, I found the PGMoney class but it too only accepts doubles. I could use doubleValue() on the BigDecimal but I am worried about the loss of precision given my past experience. So my position is that I reckon I should be able to use arbitrary precision up until the database interprets it). Cheers Mark -- Mark Lillywhite - Plastic Software http://www.plasticsoftware.com.au/ ------------------------------------------------------------------------------- Plastic Software provides fully integrated, easy to use products and support for Internet Service Providers, such as authentication and accounts receivable. "The great thing about free mail is that you don't have to pay for it." -- Scott McNealy
Mark Lillywhite <mark@plasticsoftware.com.au> writes:
> (As an aside, I found the PGMoney class but it too only accepts doubles. I
> could use doubleValue() on the BigDecimal but I am worried about the loss of
> precision given my past experience. So my position is that I reckon I should
> be able to use arbitrary precision up until the database interprets it).
Mark, I dunno what Postgres version you are using, but there is a
genuine arbitrary-precision-decimal NUMERIC type in Postgres 6.5 & up.
That's probably what you should be using. The old 'money' type is a
hack, because it overflows too easily. ($20 million ain't what it
used to be ;-).) It's now deprecated and will probably go away
entirely at some point.
regards, tom lane
Hi Tom, > Mark Lillywhite <mark@plasticsoftware.com.au> writes: > > (As an aside, I found the PGMoney class but it too only accepts doubles. I > > could use doubleValue() on the BigDecimal but I am worried about the loss of > > precision given my past experience. So my position is that I reckon I should > > be able to use arbitrary precision up until the database interprets it). > > Mark, I dunno what Postgres version you are using, but there is a > genuine arbitrary-precision-decimal NUMERIC type in Postgres 6.5 & up. > That's probably what you should be using. The old 'money' type is a > hack, because it overflows too easily. ($20 million ain't what it > used to be ;-).) It's now deprecated and will probably go away > entirely at some point. Thanks HEAPS for this, we were very confused about the numeric support because we were reading the documentation which indicated that there *was* a numeric type, but we were using the Postgres that comes on the RH6.0 CD, which is 6.4, and of course we couldn't get it to work - but "money" worked fine. We upgraded to 6.5 but we didn't try the numeric type. It's all just 'cos I'm new and don't know my way around the documentation/changelogs/readmes etc yet. Thanks again for your help, I guess my JDBC patch is kinda worthless then :) Cheers Mark
On Sat, 18 Sep 1999, Mark Lillywhite wrote: [snip] > (c) is anyone interested in > the patch, which I am happy to contribute? I would also patch the JDBC2 > driver and I intend to look for an exception message bug that seems to want > more java.text.format parameters than there are available. The first thing is to get the current driver. The 6.5.2 driver has a lot of the bugs in 6.5 and 6.5.1 fixed, including the java.text.format problems. Also, the easiest way to send patches is direct to me. I can then make sure they don't break the new stuff for 6.6, and can get them in faster. Also, it ensures that the precompiled drivers I have on my site are kept up to date. > Anyway, I know I have jumped in with both feet here but we are very happy and > impressed with Postgresql and I look forward to being able to contribute, if > possible. I have quite a few questions about postgresql in general but I will > wait until I am not such a newbie any more. So, apologies if I have done the > wrong thing here. > > (As an aside, I found the PGMoney class but it too only accepts doubles. I > could use doubleValue() on the BigDecimal but I am worried about the loss of > precision given my past experience. So my position is that I reckon I should > be able to use arbitrary precision up until the database interprets it). It shouldn't be difficult to add BigDecimal support. Peter -- Peter T Mount peter@retep.org.uk Main Homepage: http://www.retep.org.uk PostgreSQL JDBC Faq: http://www.retep.org.uk/postgresJava PDF Generator: http://www.retep.org.uk/pdf
Hi, Yeah, I realised that I should have got the latest drivers, but I wanted to see if I could fix it anyway. > The first thing is to get the current driver. The 6.5.2 driver has a lot > of the bugs in 6.5 and 6.5.1 fixed, including the java.text.format > problems. > > Also, the easiest way to send patches is direct to me. I can then make > sure they don't break the new stuff for 6.6, and can get them in faster. OK! I kinda went about this ass-about, I spose :-) Can someone tell me how DECIMAL/NUMERICs are stored? Is there somewhere I should look to find out? The end-user docs are a bit vague. Cheers Mark -- Mark Lillywhite - Plastic Software http://www.plasticsoftware.com.au/ ------------------------------------------------------------------------------- Plastic Software provides fully integrated, easy to use products and support for Internet Service Providers, such as authentication and accounts receivable. "The great thing about free mail is that you don't have to pay for it." -- Scott McNealy
Mark Lillywhite <mark@plasticsoftware.com.au> writes:
> Can someone tell me how DECIMAL/NUMERICs are stored? Is there
> somewhere I should look to find out? The end-user docs are a bit
> vague.
Use the source, Luke ;-)
src/include/utils/numeric.h and src/backend/utils/adt/numeric.c
are probably the files to look in.
IIRC from comments that Jan made, it's a pretty brain-dead
representation right now; one decimal digit per byte or something
like that. He was going to improve it to a base-10000-digit-per-short
format for speed reasons. Also, I think that there's currently a
difference between the on-disk format and the calculational in-memory
format, which would be nice to get rid of. We haven't heard from Jan
in a while, so I dunno when or if he's going to get around to that;
if you'd like to tackle it instead, go for it...
regards, tom lane