Re: 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(dot)geoghegan86(at)gmail(dot)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(at)iol(dot)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(at)iol(dot)ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

2009/10/3 Raymond O'Donnell <rod(at)iol(dot)ie>

>
> You specify the locale at the initdb stage, not when compiling.
>
> Ray.
>
>
>
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.
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(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/

2009/10/3 Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)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(at)samason(dot)me(dot)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(at)gmail(dot)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(at)samason(dot)me(dot)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(at)samason(dot)me(dot)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(at)samason(dot)me(dot)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(at)postgresql(dot)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(at)thebuild(dot)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(at)momjian(dot)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(at)thebuild(dot)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