Thread: How useful is the money datatype?
I've noticed that while you can perform various calculations on a column of type money, you can't use it or cast it as any other numeric type directly. Furthermore, it appears that since the locale being applied to the type is cluster-wide, you would need an entirely different cluster if say you had 2 web applications that were intended to store monetary amounts from different locations.
Is there an advantage to a money data type over a NUMERIC(10,2) or just representing it in lowest denomination of currency with an integer?
I've found that I unwittingly compiled PostgreSQL on my web server without specifying locale, and now the money type is represented in dollars. In order to change that, it would require a recompilation of PostgreSQL (and I'm surprised that there is no option to set locale at the database-level in the same way as collation has for 8.4).
Having a look around the archives, there seem to be some fairly old discussions of possibly removing this data type, so is it fair to assume it's probably not beneficial to use it?
Thanks
Thom
Hi Thom, Here's how I represent currency values: CREATE DOMAIN currency AS numeric(10,2); I understand money has been deprecated. It has one obvious flaw that I can think of: It cannot represent different currencies in different tuples, with a currency_id field. Regards, Peter Geoghegan
depending on the countries, etc - keep currencies in 10.4 , or you can compromise to 10.3 , otherwise you might run into problems with rounding, etc.
2009/10/3 Peter Geoghegan <peter.geoghegan86@gmail.com>
Here's how I represent currency values:
CREATE DOMAIN currency
AS numeric(10,2);
See, I can understand why someone might take the extra step to create a domain for storing monetary units. The fact that money is in the documentation, but contains no notes to explain that it's only there for backward-compatibility lead me to wonder if it still had relevance, which I gather it doesn't really. I'll avoid using it as it only appears to introduce unnecessary limitations with very little advantages.
Thom
On 03/10/2009 11:53, Grzegorz Jaśkiewicz wrote: > depending on the countries, etc - keep currencies in 10.4 , or you can > compromise to 10.3 , otherwise you might run into problems with > rounding, etc. I thought the idea of NUMERIC was that the value was exact, avoiding rounding problems that you might get with other floating-point types? Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
On Sat, 2009-10-03 at 11:33 +0100, Thom Brown wrote: > I've found that I unwittingly compiled PostgreSQL on my web server > without specifying locale, PostgreSQL isn't "compiled" with a locale or without one. > and now the money type is represented in dollars. In order to change > that, it would require a recompilation of PostgreSQL (and I'm > surprised that there is no option to set locale at the database-level > in the same way as collation has for 8.4). lc_monetary
On 03/10/2009 11:33, Thom Brown wrote: > I've found that I unwittingly compiled PostgreSQL on my web server > without specifying locale, and now the money type is represented in You specify the locale at the initdb stage, not when compiling. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
2009/10/3 Raymond O'Donnell <rod@iol.ie>
Yes, you're right. Got my wires crossed there. However, it still means locale-per-cluster which is disappointing. Ideally we'd have collation and locale per table or even per column.You specify the locale at the initdb stage, not when compiling.
Ray.
Thom
I understand it's kind of a survey, so to answer the question from my point of view:
The "money" data type is not useful at all.
--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/
2009/10/3 Grzegorz Jaśkiewicz <gryzman@gmail.com>: > depending on the countries, etc - keep currencies in 10.4 , or you can > compromise to 10.3 , otherwise you might run into problems with rounding, > etc. I myself don't find it useful to store currency values that include fractions of a cent. I'm sure that there are legitimate reasons for requiring greater precision, but none of those reasons happen to apply to me. I dare say that they don't apply to most people who want to store monetary values in a database. Regards, Peter Geoghegan
On Sat, Oct 03, 2009 at 12:20:57PM +0100, Raymond O'Donnell wrote: > I thought the idea of NUMERIC was that the value was exact, avoiding > rounding problems that you might get with other floating-point types? Nope, sorry it's still a computer and thus can't represent anything with infinite precision (just numeric fractions in PG's case, let alone irrational numbers). For example: select (numeric '1'/3) * 3; Gives me back 0.99999999999999999999. What NUMERIC datatypes allow you to do however is allow you to specify the precision used in calculations and storage (i.e. as 10 digits, four of those being fractional digits, as above). Thus you've got a chance of putting a bound on the total error that can accumulate during a calculation. For example, you can choose between storing a few more digits in your accounting tables so that when doing aggregations it comes out with the "right" number at the end---i.e. 10 orders of something cost the same as one order of 10 items. Or you set the precision to be coarser and then the values that have been rounded off will match everything else. -- Sam http://samason.me.uk/
On Sat, Oct 3, 2009 at 11:40 AM, Sam Mason <sam@samason.me.uk> wrote: > On Sat, Oct 03, 2009 at 12:20:57PM +0100, Raymond O'Donnell wrote: >> I thought the idea of NUMERIC was that the value was exact, avoiding >> rounding problems that you might get with other floating-point types? > > Nope, sorry it's still a computer and thus can't represent anything > with infinite precision (just numeric fractions in PG's case, let alone > irrational numbers). For example: > > select (numeric '1'/3) * 3; > I don't quite agree with your statement (I agree with your point, just not the way you worded it). I could make a type, 'rational', define the numerator, denominator, and do calculations like the above with zero loss. So it depends how you define 'represent'. Computers can do pretty much any type of bounded calculation given enough time and memory. merlin
2009/10/3 Grzegorz Jaśkiewicz <gryzman@gmail.com>: > depending on the countries, etc - keep currencies in 10.4 , or you can > compromise to 10.3 , otherwise you might run into problems with rounding, > etc. Keeping more digits of precision than the application actually can use is more likely to *cause* problems with rounding than solve them. For example, if you calculate interest on a balance (using floating point arithmetic) and then round it to $10.001 and store that in the balance your application will tell the user and your accounting department that they have $10 and their account. But if you do this ten times they'll mysteriously have an extra cent that the accounting department will not be able to account for. To avoid problems like this you must store precisely as many digits as the application requires. No more and no less. Intermediate calculations can be done with more precision or floating point arithmetic but you have to round or truncate before reporting the results and then store precisely the value you reported. -- greg
On Sat, Oct 03, 2009 at 11:49:50AM -0400, Merlin Moncure wrote: > On Sat, Oct 3, 2009 at 11:40 AM, Sam Mason <sam@samason.me.uk> wrote: > > it's still a computer and thus can't represent anything > > with infinite precision (just numeric fractions in PG's case, let alone > > irrational numbers). > > I don't quite agree with your statement (I agree with your point, just > not the way you worded it). Maybe I didn't emphasize "numeric" enough; the current implementation of numeric datatypes in PG does not allow fractions to be represented accurately. Is that any better? > I could make a type, 'rational', define > the numerator, denominator, and do calculations like the above with > zero loss. Yes, if you defined a datatype like this then it would be able to express a strictly larger subset of all numbers. > So it depends how you define 'represent'. > Computers can do pretty much any type of bounded calculation given > enough time and memory. Which is why I said "with infinite precision". Assuming infinite time or space doesn't seem to help with any real world problem, it's the details of the assumptions made and the use case(s) optimized for that tend to be interesting. -- Sam http://samason.me.uk/
Withing PG procedures at least in pgsql it is impossible to do 'money' calculations without a loss of precision. There is an open source library by IBM that I use in my C++ code to do this, and may be it can be incorporated into PG it is called decNumber http://speleotrove.com/decimal/decnumber.html Micropayment systems (that for example, I am implementing) require to have a reasonably good precision. Support for currencies such as yen also dictates that reasonably large numbers are supported in my case, all my money calculations are done in C++ using decNumber (which makes the only useful feature of Cobol be available in C++ :-) ) then I convert them to a string, and send via Postgres ODBC to NUMBER (19,6) field (Postgres ODBC driver does not support a 'naitive' number type, so I convert to text). On Sat, 03 Oct 2009 17:19 +0100, "Sam Mason" <sam@samason.me.uk> wrote: > On Sat, Oct 03, 2009 at 11:49:50AM -0400, Merlin Moncure wrote: > > On Sat, Oct 3, 2009 at 11:40 AM, Sam Mason <sam@samason.me.uk> wrote: > > > it's still a computer and thus can't represent anything > > > with infinite precision (just numeric fractions in PG's case, let alone > > > irrational numbers). > > > > I don't quite agree with your statement (I agree with your point, just > > not the way you worded it). > > Maybe I didn't emphasize "numeric" enough; the current implementation > of numeric datatypes in PG does not allow fractions to be represented > accurately. Is that any better? > > > I could make a type, 'rational', define > > the numerator, denominator, and do calculations like the above with > > zero loss. > > Yes, if you defined a datatype like this then it would be able to > express a strictly larger subset of all numbers. > > > So it depends how you define 'represent'. > > Computers can do pretty much any type of bounded calculation given > > enough time and memory. > > Which is why I said "with infinite precision". Assuming infinite time > or space doesn't seem to help with any real world problem, it's the > details of the assumptions made and the use case(s) optimized for that > tend to be interesting. > > -- > Sam http://samason.me.uk/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Vlad P author of C++ ORM http://github.com/vladp/CppOrm/tree/master -- http://www.fastmail.fm - One of many happy users: http://www.fastmail.fm/docs/quotes.html
On Sat, Oct 03, 2009 at 10:14:53PM -0400, V S P wrote: > Withing PG procedures at least in pgsql it is impossible to do 'money' > calculations without a loss of precision. The point is that on *any* computer it's impossible to perform arbitrary calculations to infinite precision (i.e. "without a loss of precision as you put it). You can do things losslessly in certain common situations and the numeric type in PG helps with a lot of these. > There is an open source library by IBM that I use in my C++ code to do > this, and may be it can be incorporated into PG > > it is called decNumber > http://speleotrove.com/decimal/decnumber.html How would this help over PG's existing numeric type? Support for decimal floating point numbers would be nice, but I'm pretty sure you're not asking for this. > Micropayment systems (that for example, I am implementing) require to > have a reasonably good precision. Support for currencies such as yen > also dictates that reasonably large numbers are supported Which limits do you find to be problematic in PG? -- Sam http://samason.me.uk/
On Sun, 4 Oct 2009, Sam Mason wrote: >> Withing PG procedures at least in pgsql it is impossible to do 'money' >> calculations without a loss of precision. > > The point is that on *any* computer it's impossible to perform arbitrary > calculations to infinite precision (i.e. "without a loss of precision as > you put it). I've not followed this tread, but read this one message, so perhaps my comments are not appropriate. In that case, I apologize for jumping in. Monetary values have always been an issue with computers. For a while, at least in the mainframe world of decades ago, binary-coded decimals (BCD) were a working approach. In the early and mid-1980s we used a procedure for business applications involving money that worked regardless of programming language or platform. To each (float, real) monetary amount we added 0.005 and truncated the result to two digits on the right of the decimal point. In almost all cases, this allowed financial calculations to be correct to the nearest penny. Financial calculations are still imperfect. Now and then I see this in both my business and personal bank statements when reconciliation is off by a penny or two. The transaction amounts (debits and credits) match, but the bank comes out with a different total than do I. This is usually only for a month or two before we are once again in agreement. Rich
On Sun, Oct 04, 2009 at 09:31:02AM -0700, Rich Shepard wrote: > On Sun, 4 Oct 2009, Sam Mason wrote: > >The point is that on *any* computer it's impossible to perform arbitrary > >calculations to infinite precision (i.e. "without a loss of precision as > >you put it). > > I've not followed this tread, but read this one message, so perhaps my > comments are not appropriate. In that case, I apologize for jumping in. More comments are normally good! > Monetary values have always been an issue with computers. For a while, at > least in the mainframe world of decades ago, binary-coded decimals (BCD) > were a working approach. I've never had to use BCDs for anything real, but I believe the reason they're nice is that when you get a result you can't represent accurately then it tends to get rounded to something that will always look nicer than when you're working in base 2. PG's numeric type effectively uses base 10 internally so would be a good fit for cases when you used to use BCD numeric encodings before. > In the early and mid-1980s we used a procedure for business applications > involving money that worked regardless of programming language or platform. > To each (float, real) monetary amount we added 0.005 and truncated the > result > to two digits on the right of the decimal point. In almost all cases, this > allowed financial calculations to be correct to the nearest penny. I was under the impression that floats have about 6 useful decimal digits of precision, thus any calculations involving units of a 100 thousand or more would start to give arbitrary values to the cents. > Financial calculations are still imperfect. Now and then I see this in > both my business and personal bank statements when reconciliation is off by > a penny or two. The transaction amounts (debits and credits) match, but the > bank comes out with a different total than do I. This is usually only for a > month or two before we are once again in agreement. That seems to be the bug that Greg Stark noted in this thread; the bank is probably storing values with more precision than it's choosing to report to you. Thus the totals will drift into and out of being correct over time. -- Sam http://samason.me.uk/
Rich Shepard wrote: > > In the early and mid-1980s we used a procedure for business > applications > involving money that worked regardless of programming language or > platform. > To each (float, real) monetary amount we added 0.005 and truncated the > result > to two digits on the right of the decimal point. In almost all cases, > this > allowed financial calculations to be correct to the nearest penny. > > Financial calculations are still imperfect. Now and then I see this in > both my business and personal bank statements when reconciliation is > off by > a penny or two. The transaction amounts (debits and credits) match, > but the > bank comes out with a different total than do I. This is usually only > for a > month or two before we are once again in agreement. > > Rich > > Rich what causes the difference you are referring to is method used to round, bankers rounding aka (round to even) vs basic rounding we are taught in school aka (round half up). http://en.wikipedia.org/wiki/Rounding General what i do is leave more digits in the number than is needed then round after all the calculations are done... A common problem applications/databases suffer from is inconsistent precision. In one place the database is using 4 digits another 6 in another 0 and in another 2 digits. Be consistent in the use of precision if not, be prepared to untangle a nightmare. The money type i have found is absolutely worthless when doing math but using it to simplify formating great. select 123456789::text::money; set session lc_monetary to 'fr_FR.UTF-8'; select 123456789::text::money
Rich Shepard wrote: > On Sun, 4 Oct 2009, Sam Mason wrote: > >>> Withing PG procedures at least in pgsql it is impossible to do 'money' >>> calculations without a loss of precision. >> >> The point is that on *any* computer it's impossible to perform arbitrary >> calculations to infinite precision (i.e. "without a loss of precision as >> you put it). > Monetary values have always been an issue with computers. For a while, at > least in the mainframe world of decades ago, binary-coded decimals (BCD) > were a working approach. Yes, packed decimal is a standard way to handle money with no loss of precision. And "for a while" would be over 50 years, as packed decimal is still in use today. All banks rely on it. The best way to avoid loss of precision with decimal is to use decimal representation, and not convert to binary at all. There is no reason why PG could not support packed decimal. -- Guy Rouillier
On Oct 4, 2009, at 7:09 PM, Guy Rouillier wrote:
> There is no reason why PG could not support packed decimal.
Is that not NUMERIC?
--
-- Christophe Pettus
xof@thebuild.com
Christophe Pettus wrote: > > On Oct 4, 2009, at 7:09 PM, Guy Rouillier wrote: >> There is no reason why PG could not support packed decimal. > > Is that not NUMERIC? No, that is not NUMERIC. All numeric types are stored as binary representations. Packed decimal is not. Perhaps an example would clarify. The number 1234 would be represented as follows: binary: 10011010010 packed decimal: 12 34 Packed decimal needs to be able to represent 10 distinct characters, 0-9, so it uses half a byte for each. So a 4 digit number can be represented in 2 bytes (for simplicity, I'm ignoring sign. That takes a half byte.) The IBM implementation provides a corresponding arithmetic library to use packed decimal. These numbers are never converted to binary, so there is no loss in precision. -- Guy Rouillier
Guy Rouillier wrote:
> Christophe Pettus wrote:
> >
> > On Oct 4, 2009, at 7:09 PM, Guy Rouillier wrote:
> >> There is no reason why PG could not support packed decimal.
> >
> > Is that not NUMERIC?
>
> No, that is not NUMERIC. All numeric types are stored as binary
> representations. Packed decimal is not. Perhaps an example would
> clarify. The number 1234 would be represented as follows:
I think you are wrong. The Postgres documentation say:
The type <type>numeric</type> can store numbers with up to 1000
digits of precision and perform calculations exactly. It is
especially recommended for storing monetary amounts and other
quantities where exactness is required. However, arithmetic on
<type>numeric</type> values is very slow compared to the integer
types, or to the floating-point types described in the next
section.
Postgres NUMERIC is packed decimal, base 1000, as I remember.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
A quick check of the source code (src/backend/utils/adt/numeric.c)
shows it's base 10000, each "digit" represented as an int16. It's not
strictly speaking BCD, but there's no computational difference.
--
-- Christophe Pettus
xof@thebuild.com
On Mon, Oct 05, 2009 at 01:07:16PM -0700, Christophe Pettus wrote: > A quick check of the source code (src/backend/utils/adt/numeric.c) > shows it's base 10000, each "digit" represented as an int16. I was going to note that in my post but thought it was needless detail; ah well, maybe next time I will! :) -- Sam http://samason.me.uk/
Bruce Momjian wrote: > Guy Rouillier wrote: >> Christophe Pettus wrote: >>> On Oct 4, 2009, at 7:09 PM, Guy Rouillier wrote: >>>> There is no reason why PG could not support packed decimal. >>> Is that not NUMERIC? >> No, that is not NUMERIC. All numeric types are stored as binary >> representations. Packed decimal is not. Perhaps an example would >> clarify. The number 1234 would be represented as follows: > > I think you are wrong. The Postgres documentation say: You are correct, I am wrong, as private emails also pointed out. I should read more carefully. This list is rapidly self-correcting ;). Thanks. The IBM implementation provided language libraries (usually COBOL) that also supported packed decimal, so precision was maintained throughout the entire application stack. -- Guy Rouillier
Guy Rouillier wrote: > The IBM implementation provided language libraries (usually COBOL) > that also supported packed decimal, so precision was maintained > throughout the entire application stack. > IBM 360/370/390/etcetc/Zsystem has BCD op codes in the instruction set architecture. microcoded of course, but they generally ran at the memory bandwidth. but, postgres's base 10000 'numeric' datatype is perfectly useful for this. high precision numeric divides are probably fugly, but for normal business math, they perform quite nicely.
On Sun, 2009-10-04 at 17:12 +0100, Sam Mason wrote: > > There is an open source library by IBM that I use in my C++ code to do > > this, and may be it can be incorporated into PG > > > > it is called decNumber > > http://speleotrove.com/decimal/decnumber.html > > How would this help over PG's existing numeric type? I don't see it either. Pg's NUMERIC type is quite suitable for mixing large and small values in calculations with reasonable precision. test=> SELECT NUMERIC '100000000' * NUMERIC '0.00000000009'; 0.00900000000 test=> SELECT NUMERIC '10000090009.000000000000000001' / NUMERIC '0.0000000000000000000000002'; 50000450045000000000000000005000000.0000000000000000000000000 Sometimes you have to be very careful to make sure that Pg interprets operands as NUMERIC, though, rather than as floats. I've been extremely happy with the NUMERIC data type when working with monetary values. For scientific use I wish there was an equivalent type with explicit accumulation of error so I knew how much of the value was valid, but you don't need that for financial calculations. -- Craig Ringer