How useful is the money datatype?

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>

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.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