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!

Mon, 13 Dec 1999 11:57:23 +0100
Michael Meskes <meskes@postgresql.org>
Datatype MONEY
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


Mon, 13 Dec 1999 12:25:28 +0100 (MET)
Peter Eisentraut <e99re41@DoCS.UU.SE>
Re: [HACKERS] Datatype MONEY

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


Mon, 13 Dec 1999 13:13:55 +0100 (CET)
Karel Zak - Zakkr <zakkr@zf.jcu.cz>
Re: [HACKERS] Datatype MONEY
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) #



Mon, 13 Dec 1999 13:34:54 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Datatype MONEY

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


  


Mon, 13 Dec 1999 14:37:44 +0100 (CET)
Karel Zak - Zakkr <zakkr@zf.jcu.cz>
Re: [HACKERS] Datatype MONEY
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) #



Mon, 13 Dec 1999 15:03:08 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Datatype MONEY



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




Mon, 13 Dec 1999 15:14:32 +0100 (CET)
Karel Zak - Zakkr <zakkr@zf.jcu.cz>
Re: [HACKERS] Datatype MONEY
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!

Mon, 13 Dec 1999 15:26:39 +0100
Michael Meskes <meskes@postgreSQL.org>
Re: [HACKERS] Datatype MONEY
>  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) #



Mon, 13 Dec 1999 15:52:25 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Datatype MONEY
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) #



Mon, 13 Dec 1999 16:13:29 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Datatype MONEY
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

Mon, 13 Dec 1999 11:11:50 -0500
Tom Lane <tgl@sss.pgh.pa.us>
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@aurora.rg.iupui.edu------------------#include <usual/disclaimer>
Mon, 13 Dec 1999 11:54:04 -0500
Gunther Schadow <gunther@aurora.rg.iupui.edu>
Re: [HACKERS] Datatype MONEY
> 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) #



Mon, 13 Dec 1999 18:14:31 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Datatype MONEY


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


Tue, 14 Dec 1999 15:54:10 +0100 (CET)
Karel Zak - Zakkr <zakkr@zf.jcu.cz>
Re: [HACKERS] Datatype MONEY