Thread: Datatype MONEY
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. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL!
Use DECIMAL/NUMERIC. Money is deprecated, slightly broken, and will eventually disappear. There are some thoughts of re-implementing it on top of numeric as a collection of formatting functions in essence, so with numeric (or decimal) you'll be fit for the future. On Mon, 13 Dec 1999, 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. > > Michael > -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
On Mon, 13 Dec 1999, 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.
>
> Michael
I have complete code for numbers formatting (to_char() compatible with
Oracle). It allow you add a currency symbol corresponding with current
locale ... and more features over basic datatypes (float4/8, int4/8).
I send it to the PACHES list next week (probably).
Example:
template1=> select float8_to_char(455.9 , 'L999D99') as price;
price
---------
Kc 455,90
(1 row)
(It is with Czech currency symbol and decimal point (locales))
IMHO is good use for money a float type.
Karel
Karel Zak - Zakkr wrote:
> On Mon, 13 Dec 1999, Michael Meskes wrote:
>
> > I somehow remember the MONEY datatype has some problems and might be
> > removed. Now I didn=B4t follow this topic closely enough, but now I've
> > encountered I could use it pretty well. Of course a DECIMAL datatype fi=
> ts
> > the bill as good since I do not need the currency symbol in psql's outp=
> ut.
> >=20
> > Before I set up my DB I'd like to know which type to prefer.
> >=20
> > Michael
>
> I have complete code for numbers formatting (to_char() compatible with
> Oracle). It allow you add a currency symbol corresponding with current
> locale ... and more features over basic datatypes (float4/8, int4/8).
>
> I send it to the PACHES list next week (probably). =20
>
>
> Example:
>
> template1=3D> select float8_to_char(455.9 , 'L999D99') as price;
> price
> ---------
> Kc 455,90
> (1 row)
>
> (It is with Czech currency symbol and decimal point (locales))
>
> IMHO is good use for money a float type.
In some countries (Germany at least) storage of financial
booking information is not permitted to use floats. And you
aren't allowed to use it for calculation of taxes etc.,
instead you must use some datatype with a fixable number of
digits after the decimal point.
Thus, only our NUMERIC/DECIMAL type or int4/8 and using the
'V' (IIRC) format specifier in to_char() should be used.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
On Mon, 13 Dec 1999, Jan Wieck wrote:
> Karel Zak - Zakkr wrote:
> >
> > IMHO is good use for money a float type.
>
> In some countries (Germany at least) storage of financial
> booking information is not permitted to use floats. And you
> aren't allowed to use it for calculation of taxes etc.,
> instead you must use some datatype with a fixable number of
> digits after the decimal point.
>
> Thus, only our NUMERIC/DECIMAL type or int4/8 and using the
> 'V' (IIRC) format specifier in to_char() should be used.
Hmm, interesting.. but it is not problem for to_char(), it is problem
(how number datetype choise) for users.
To_char() formatting numbers by course of format-picture (second arg.)
only - total all is user choise (how set format), and to_char() not check
if country form allow to use fixet/notfixet digits after the decimal point
(in locales is not information about it, or yes?).
I take back my previous "IMHO".
But if you use to_char(444.555, '999.99'), output is always with two digits
after the decimal point and our country form is pleased ... I agree, it is
only output option, internaly is still problem if you will calculate with
float.
Or is other idea for to_char() money formatting and how datetype must be
supported (I plan float4/8 int4/8 now)?
(note: 'V' format specifier is multiplier and return a value as 10^n).
Karel
Karel Zak - Zakkr wrote:
>
> On Mon, 13 Dec 1999, Jan Wieck wrote:
>
> > In some countries (Germany at least) storage of financial
> > booking information is not permitted to use floats. And you
>
> Hmm, interesting.. but it is not problem for to_char(), it is problem
> (how number datetype choise) for users.
But it is subject for what would happen in the expression
first if you have both, to_char(float8, text) and
to_char(numeric, text) available and execute a query with
to_char(444.55, '9999.99').
If the parser could choose to read in the value as float8 and
pass that to to_char(float8, text), the system would not be
compliant to financial software requirements in Germany.
> Or is other idea for to_char() money formatting and how datetype must be
> supported (I plan float4/8 int4/8 now)?
You should at least add NUMERIC to possible inputs. Otherwise
there would be no chance than to convert it to float8,
possibly loosing significant digits (and becoming not
compliant as to above).
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
On Mon, 13 Dec 1999, Jan Wieck wrote:
> Karel Zak - Zakkr wrote:
> >
> > On Mon, 13 Dec 1999, Jan Wieck wrote:
> >
> > > In some countries (Germany at least) storage of financial
> > > booking information is not permitted to use floats. And you
> >
> > Hmm, interesting.. but it is not problem for to_char(), it is problem
> > (how number datetype choise) for users.
>
> But it is subject for what would happen in the expression
> first if you have both, to_char(float8, text) and
> to_char(numeric, text) available and execute a query with
> to_char(444.55, '9999.99').
>
> If the parser could choose to read in the value as float8 and
> pass that to to_char(float8, text), the system would not be
> compliant to financial software requirements in Germany.
Hmm, it is very firm in Germany (or in EU?) if not allow to use float
in financ. software, I must ask about it how is it in Czech. Thank for
interesting information :-)
> > Or is other idea for to_char() money formatting and how datetype must be
> > supported (I plan float4/8 int4/8 now)?
>
> You should at least add NUMERIC to possible inputs. Otherwise
> there would be no chance than to convert it to float8,
> possibly loosing significant digits (and becoming not
> compliant as to above).
>
Well, on a datetype is depend only small part in to_char(), I try
write to_char(numeric, text) version. But I must first explore
NUMERIC datetupe... (documentation is quiet for this).
Thank Jan for suggestion.
Karel
On Mon, Dec 13, 1999 at 01:13:55PM +0100, Karel Zak - Zakkr wrote: > I have complete code for numbers formatting (to_char() compatible with > Oracle). It allow you add a currency symbol corresponding with current Sounds good. > locale ... and more features over basic datatypes (float4/8, int4/8). Not about DECIMAL/NUMERIC? I don't like the idea of doing currecny calculations with floats. BTW could anyone tell me how exactly NUMERIC is stored? Just for curiosity. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL!
> Well, on a datetype is depend only small part in to_char(), I try
> write to_char(numeric, text) version. But I must first explore
> NUMERIC datetupe... (documentation is quiet for this).
NUMERIC's output function returns a null terminated string
representation as usual. Possibly a dash (negative sign), one
or more digits, optionally followed by a decimal point and
one or more digits. And you could get it with an adjusted
number of digits after the decimal point by doing
text *numeric_to_char(Numeric num, format text)
{
char *numstr;
int32 scale;
... /* calculate the wanted number of digits */
... /* after DP in scale depending on format */
numstr = numeric_out(numeric_round(num, scale));
}
There will be "scale" number of digits after the DP, which is
missing if scale is zero. The value will be correct rouded
and/or zero padded at the end.
Wouldn't that be enough for you?
Well, you must work on the string only and cannot read it
into a float internally, but with the above preprocessing, it
should be fairly simple.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
Michael Meskes wrote:
> On Mon, Dec 13, 1999 at 01:13:55PM +0100, Karel Zak - Zakkr wrote:
> > I have complete code for numbers formatting (to_char() compatible with
> > Oracle). It allow you add a currency symbol corresponding with current
>
> Sounds good.
>
> > locale ... and more features over basic datatypes (float4/8, int4/8).
>
> Not about DECIMAL/NUMERIC? I don't like the idea of doing currecny
> calculations with floats.
First it's a variable size datatype. There's some information
about weight of first digit, precision, scale and sign.
Following are all digits coded into nibbles (4-bit per
digit).
The weight tells which of the digits WRT to the decimal point
the first nibble contains. Precision and scale tell how many
digits at all and after DP to have. Leading and trailing zero
digits are stripped off in the DB stored value with an
adjusted weight, so a 5000000000000 value with a precision of
200 digits will only occupy one nibble when stored. A single
5 with a weight of 12.
If I ever find the time (soonest 2001 I expect) I'll
completely replace the digit storage by small integers and
store the value in base 10000 instead of 10. Just for
performance reasons.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
Michael Meskes <meskes@postgreSQL.org> writes:
> BTW could anyone tell me how exactly NUMERIC is stored? Just for curiosity.
I believe it's a simple-minded BCD format, one decimal digit per byte.
Jan has been muttering about reimplementing it as radix-10000, storing
four decimal digits per short instead of one per byte; that'd reduce
the number of iterations in the inner calculation loops by 4x, without
making the elementary steps noticeably more expensive on modern hardware...
regards, tom lane
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@aurora.rg.iupui.edu------------------#include <usual/disclaimer>
> 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!
The latter is IMHO the better. If you have a foreign currency
account, it's balance will not raise and fall as exchange
rates change. That's what they are good for. Only at the
time, you transfer money between different currency accounts,
the actual exchange rate is used.
Keeping track of hourly/dayly exchange rates is only good if
you need reports for controlling purposes. There it's better
to have anything converted into your inhouse currency. View's
do a wonderful job here.
BTW: The non-floating-point restriction does NOT apply to
controlling systems, because they are management information
systems and not subject to the Ministry of Finance, as the
bookkeeping data is.
For those who wonder: between 1980 and 1983 I learned, and
until 1987 I worked as a bank clerk. That left some traces
that sometimes are useful.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
On Mon, 13 Dec 1999, Jan Wieck wrote:
> text *numeric_to_char(Numeric num, format text)
> {
> char *numstr;
> int32 scale;
>
> ... /* calculate the wanted number of digits */
> ... /* after DP in scale depending on format */
>
> numstr = numeric_out(numeric_round(num, scale));
> }
>
> There will be "scale" number of digits after the DP, which is
> missing if scale is zero. The value will be correct rouded
> and/or zero padded at the end.
>
> Wouldn't that be enough for you?
My answer :-)
test=> select numeric_to_char(545454.98, '"der Preis: "L999G999D99');
numeric_to_char
------------------------
der Preis: DM 545.454,98
(1 row)
> Well, you must work on the string only and cannot read it
> into a float internally, but with the above preprocessing, it
> should be fairly simple.
Yes, I good understend your previous letter(s). Formatting routine in
to_char() is independent on datetype and for all use string.
(IMHO numeric is very interesting type and not has 16-decimal limitation as
float8, it is good, good, good...
Again Thank! Karel
----------------------------------------------------------------------
Karel Zak <zakkr@zf.jcu.cz> http://home.zf.jcu.cz/~zakkr/
Docs: http://docs.linux.cz (big docs archive)
Kim Project: http://home.zf.jcu.cz/~zakkr/kim/ (process manager)
FTP: ftp://ftp2.zf.jcu.cz/users/zakkr/ (C/ncurses/PgSQL)
-----------------------------------------------------------------------