Re: [HACKERS] Datatype MONEY

Michael Meskes wrote:
>
> I somehow remember the MONEY datatype has some problems and might be
> removed. Now I didn´t follow this topic closely enough, but now I've
> encountered I could use it pretty well. Of course a DECIMAL datatype fits
> the bill as good since I do not need the currency symbol in psql's output.
>
> Before I set up my DB I'd like to know which type to prefer.

AFAIK the MONEY data type in SQL is a toy rather than a serious thing.
It makes a big deal out of locale-dependent currency symbols but that
way lacks robustness: try the following game:

locale = INDIA (currency 1 RUPEE <= 1/40 US$)

UPDATE bankAccounts SET balance='10000 Rs.' WHERE id='123'

then switch your locale to USA (currency 1 US$ >= 40 Rs.)

SELECT balance FROM bankAccounts WHERE id='123'

-> 10000 US$

You have just got your rupees converted at an exceptional exchange rate
of 1:1!!!

In my opinion locale should not affect what gets stored in the data
base and local should not change the meaning of the data. So using
the locale for currency symbol naively can be problematic. What you
need to do to really support money in different currencies is keep
track of your hourly exchange rates etc. Then store your data in
one currency as a DECIMAL or whatever. Alternatively, store the pair
(value DECIMAL, currency CHAR(3)) in the data base, with currency
being the ISO 3-letter code. Be aware of the difference in semantics!

regards
-Gunther

--
Gunther_Schadow-------------------------------http://aurora.rg.iupui.edu
Regenstrief Institute for Health Care
1050 Wishard Blvd., Indianapolis IN 46202, Phone: (317) 630 7960
schadow(at)aurora(dot)rg(dot)iupui(dot)edu------------------#include <usual/disclaimer>