Re: Format of the Money field

When did the MONEY type change it's output format?

While working on the JDBC test suite, Money broke. It seems to output:
$10.99
($10.99) for negative values

While since ages past, the PGMoney class interprets it as a number (no currency
symbol).

Peter

--
Peter Mount peter(at)retep(dot)org(dot)uk
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/

On Fri, 2 Feb 2001, Peter T Mount wrote:

> When did the MONEY type change it's output format?
>
> While working on the JDBC test suite, Money broke. It seems to output:
> $10.99
> ($10.99) for negative values
>
> While since ages past, the PGMoney class interprets it as a number (no
> currency symbol).

Looking over at Thomas and asking him, his recollection is that it always
had the currency symbol ... but he's not 100% certain about that ...

Can you confirm with the 7.0.3 server?

hhs=> select version();
version
-------------------------------------------------------------------
PostgreSQL 6.4.2 on i386-unknown-freebsd3.1, compiled by gcc 2.7.2.

| currentsalary | money |
4 |

hhs=> select currentsalary from applicants;

$77,000.00
$43,500.00
$0.00
$93,000.00
...

If it changed, it looks like it changed a long time ago! :-)

-Mitch

----- Original Message -----
From: "The Hermit Hacker" <scrappy(at)hub(dot)org>
To: "Peter T Mount" <peter(at)retep(dot)org(dot)uk>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Sent: Friday, February 02, 2001 11:55 AM
Subject: Re: Format of the Money field

> On Fri, 2 Feb 2001, Peter T Mount wrote:
>
> > When did the MONEY type change it's output format?
> >
> > While working on the JDBC test suite, Money broke. It seems to output:
> > $10.99
> > ($10.99) for negative values
> >
> > While since ages past, the PGMoney class interprets it as a number (no
> > currency symbol).
>
> Looking over at Thomas and asking him, his recollection is that it always
> had the currency symbol ... but he's not 100% certain about that ...
>
> Can you confirm with the 7.0.3 server?
>
>
>

At 12:07 02/02/01 -0500, Mitch Vincent wrote:

>hhs=> select version();
>version
>-------------------------------------------------------------------
>PostgreSQL 6.4.2 on i386-unknown-freebsd3.1, compiled by gcc 2.7.2.

[snip]

> If it changed, it looks like it changed a long time ago! :-)

Hmm, shows how many people use Money via JDBC then, as no one's reported it
before. I only found out while testing JBuilder's interaction with the JDBC
driver.

Peter

>-Mitch
>
>
>----- Original Message -----
>From: "The Hermit Hacker" <scrappy(at)hub(dot)org>
>To: "Peter T Mount" <peter(at)retep(dot)org(dot)uk>
>Cc: <pgsql-hackers(at)postgresql(dot)org>
>Sent: Friday, February 02, 2001 11:55 AM
>Subject: Re: Format of the Money field
>
>
> > On Fri, 2 Feb 2001, Peter T Mount wrote:
> >
> > > When did the MONEY type change it's output format?
> > >
> > > While working on the JDBC test suite, Money broke. It seems to output:
> > > $10.99
> > > ($10.99) for negative values
> > >
> > > While since ages past, the PGMoney class interprets it as a number (no
> > > currency symbol).
> >
> > Looking over at Thomas and asking him, his recollection is that it always
> > had the currency symbol ... but he's not 100% certain about that ...
> >
> > Can you confirm with the 7.0.3 server?
> >
> >
> >

At 12:55 02/02/01 -0400, The Hermit Hacker wrote:
>On Fri, 2 Feb 2001, Peter T Mount wrote:
>
> > When did the MONEY type change it's output format?
> >
> > While working on the JDBC test suite, Money broke. It seems to output:
> > $10.99
> > ($10.99) for negative values
> >
> > While since ages past, the PGMoney class interprets it as a number (no
> > currency symbol).
>
>Looking over at Thomas and asking him, his recollection is that it always
>had the currency symbol ... but he's not 100% certain about that ...
>
>Can you confirm with the 7.0.3 server?

As I just said to Mitch (who tested against 6.4 and it had the currency
symbol) no one must use Money via JDBC as no one's found it before. The
PGMoney class dates from the 6.2/6.3 days so it might date back that far.
I'll fix it anyhow.

Peter

Just a question on this for my own personal satisfaction...

What's the standard on Money type (if there is one) and if it doesn't
include the $ (of course that would change based on what currency you were
using) then is it any different than numeric(9,2)? numeric(9,2) is what I
use for all fields that need to hold a dollar amount so I'm curious.. I
remember reading in the documentation that money was numeric(9,2) with the
dollar sign added but I wanted to check with the man :-)

-Mitch

----- Original Message -----
From: "Peter Mount" <peter(at)retep(dot)org(dot)uk>
To: "Mitch Vincent" <mitch(at)venux(dot)net>; "The Hermit Hacker" <scrappy(at)hub(dot)org>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Sent: Saturday, February 03, 2001 5:50 AM
Subject: Re: Format of the Money field

> At 12:07 02/02/01 -0500, Mitch Vincent wrote:
>
> >hhs=> select version();
> >version
> >-------------------------------------------------------------------
> >PostgreSQL 6.4.2 on i386-unknown-freebsd3.1, compiled by gcc 2.7.2.
>
> [snip]
>
>
> > If it changed, it looks like it changed a long time ago! :-)
>
> Hmm, shows how many people use Money via JDBC then, as no one's reported
it
> before. I only found out while testing JBuilder's interaction with the
JDBC
> driver.
>
> Peter
>
> >-Mitch
> >
> >
> >----- Original Message -----
> >From: "The Hermit Hacker" <scrappy(at)hub(dot)org>
> >To: "Peter T Mount" <peter(at)retep(dot)org(dot)uk>
> >Cc: <pgsql-hackers(at)postgresql(dot)org>
> >Sent: Friday, February 02, 2001 11:55 AM
> >Subject: Re: Format of the Money field
> >
> >
> > > On Fri, 2 Feb 2001, Peter T Mount wrote:
> > >
> > > > When did the MONEY type change it's output format?
> > > >
> > > > While working on the JDBC test suite, Money broke. It seems to
output:
> > > > $10.99
> > > > ($10.99) for negative values
> > > >
> > > > While since ages past, the PGMoney class interprets it as a number
(no
> > > > currency symbol).
> > >
> > > Looking over at Thomas and asking him, his recollection is that it
always
> > > had the currency symbol ... but he's not 100% certain about that ...
> > >
> > > Can you confirm with the 7.0.3 server?
> > >
> > >
> > >
>
>

I acknowledged the bad nature of the money field (pretty clearly stated in
my email, I think).. I agree, it shouldn't contain a sign of anything.. My
applications are used in the US and in the US only so I don't have issue
with the currency symbol. I don't use the money type anyway (the example I
used was from someone else's code!).. What I was actually asking about was
the need for the money type, the same thing can be achieved using the other
data types (in fact the documentation lists money as numeric(9,2) with the $
added I believe).. All that for exactly what you said, currency. There are
as many currencies as countries (almost) so I totally agree, a symbol is A
Bad Thing(TM).. You're also right (and bring up a good point) about the
storing of money in the smallest unit if you're coding international... I
haven't had to yet but it's something I'll be sure to do if it ever comes
up..

Of course all this is moot, Peter already said he was changing it and that
it shouldn't have been that way, it's just been overlooked (probably because
no one is using the money type)! :-)

I apologize to the list, I meant to send that email directly to Peter -- I
was too quick on the send..

-Mitch

----- Original Message -----
From: "Dave Mertens" <dave(at)redbull(dot)zyprexia(dot)com>
To: <pgsql-hackers(at)postgresql(dot)org>
Sent: Saturday, February 03, 2001 2:12 PM
Subject: Re: Re: Format of the Money field

> On Sat, Feb 03, 2001 at 11:39:29AM -0500, Mitch Vincent wrote:
> > What's the standard on Money type (if there is one) and if it doesn't
> > include the $ (of course that would change based on what currency you
were
> > using) then is it any different than numeric(9,2)? numeric(9,2) is what
I
> > use for all fields that need to hold a dollar amount so I'm curious.. I
> > remember reading in the documentation that money was numeric(9,2) with
the
> > dollar sign added but I wanted to check with the man :-)
>
> Oh, never heard of currency?? NOT every country is using dollars. In a few
> months we in Europe are going to use the Euro. A money-type is normaly a
> floating type with a precision of 5 (float(5)). A money field is just like
> an float, only less precies. By the way, storing money values with an
> decimal precision is a (mostly) a bad thing. We Save currency amounts in
> the smallest unit. We save every amount in Eurocents. Our programs format
> the amount to the proper format (US-format (35,673.56) or EuropeannFormat
> (35.673,56). Currency signs are bad things in databases. Most database are
> international, so most amounts also!
>
> Sorry for this hard correction.
>
> Dave Mertens
> System Administrator ISM, Netherlands
> dmertens(at)ism(dot)nl
>

On Sat, Feb 03, 2001 at 11:39:29AM -0500, Mitch Vincent wrote:
> What's the standard on Money type (if there is one) and if it doesn't
> include the $ (of course that would change based on what currency you were
> using) then is it any different than numeric(9,2)? numeric(9,2) is what I
> use for all fields that need to hold a dollar amount so I'm curious.. I
> remember reading in the documentation that money was numeric(9,2) with the
> dollar sign added but I wanted to check with the man :-)

Oh, never heard of currency?? NOT every country is using dollars. In a few
months we in Europe are going to use the Euro. A money-type is normaly a
floating type with a precision of 5 (float(5)). A money field is just like
an float, only less precies. By the way, storing money values with an
decimal precision is a (mostly) a bad thing. We Save currency amounts in
the smallest unit. We save every amount in Eurocents. Our programs format
the amount to the proper format (US-format (35,673.56) or EuropeannFormat
(35.673,56). Currency signs are bad things in databases. Most database are
international, so most amounts also!

Sorry for this hard correction.

Dave Mertens
System Administrator ISM, Netherlands
dmertens(at)ism(dot)nl

On Sat, 3 Feb 2001, Dave Mertens wrote:

> On Sat, Feb 03, 2001 at 11:39:29AM -0500, Mitch Vincent wrote:
> > What's the standard on Money type (if there is one) and if it doesn't
> > include the $ (of course that would change based on what currency you were
> > using) then is it any different than numeric(9,2)? numeric(9,2) is what I
> > use for all fields that need to hold a dollar amount so I'm curious.. I
> > remember reading in the documentation that money was numeric(9,2) with the
> > dollar sign added but I wanted to check with the man :-)

This is possible if you use for formatting to_char()/to_number() with
full locale support (here for example with de_DE locale):

test=# select to_char(1000.12, 'L9G999D99');
to_char
-------------
DM 1.000,12
(1 row)

test=# select to_number('DM 1.000,12', 'L9G999D99');
to_number
-----------
1000.12
(1 row)

IMHO use numeric and some formatting routine is good idea (better than
current money datetype..)

> Oh, never heard of currency?? NOT every country is using dollars. In a few
> months we in Europe are going to use the Euro. A money-type is normaly a

BTW, the other day I read nice paper about some US-banks that not known
Euro yet, and have problems with cheque in Euro... :-)

How locale is needful for Euro currency symbol? :-)

> floating type with a precision of 5 (float(5)). A money field is just like

A float for money? Not sure that it is normaly. See archive (already
discussed), here is a part or old Jan's letter:

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

> an float, only less precies. By the way, storing money values with an
> decimal precision is a (mostly) a bad thing. We Save currency amounts in
> the smallest unit. We save every amount in Eurocents. Our programs format
> the amount to the proper format (US-format (35,673.56) or EuropeannFormat
> (35.673,56). Currency signs are bad things in databases. Most database are
> international, so most amounts also!

In PostgreSQL it is output thing, a currency symbol is not inside DB.

Karel

> IMHO use numeric and some formatting routine is good idea (better than
> current money datetype..)

The "money" type implementation was a workaround/hack to make up for the
lack of a "numeric" type. I've always assumed that it would be removed
as soon as numeric was available and fast enough to meet the needs. At
the moment "money" uses a 32-bit integer for its implementation, which
is not adequate for most large financial applications.

At some point we might want to repackage "money" as a thin wrapper over
"numeric" which adds currency symbols etc.

- Thomas