Thread: Re: [PATCHES] Fixes for MONEY type using locale
D'Arcy J.M. Cain wrote: > On Sat, 24 Nov 2007 11:27:38 -0500 (EST) > Bruce Momjian <bruce@momjian.us> wrote: > > I am confused about two other items with MONEY. First, why can't > > anything but a string be cast to this type? > > > > test=> select 871234872319489323::money; > > ERROR: cannot cast type bigint to money > > LINE 1: select 871234872319489323::money; > > ^ > > test=> select 871234872::money; > > ERROR: cannot cast type integer to money > > LINE 1: select 871234872::money; > > ^ > > test=> select 87123487231.3::money; > > ERROR: cannot cast type numeric to money > > LINE 1: select 87123487231.3::money; > > ^ > > I agree. I wasn't the one that added the meta information. OK, so the big question then is if we are un-depricating this data type, does it have the behavior we want? (And the regression addition will be helpful too.) -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote:
> D'Arcy J.M. Cain wrote:
> > On Sat, 24 Nov 2007 11:27:38 -0500 (EST)
> > Bruce Momjian <bruce@momjian.us> wrote:
> > > I am confused about two other items with MONEY. First, why can't
> > > anything but a string be cast to this type?
> > >
> > > test=> select 871234872319489323::money;
> > > ERROR: cannot cast type bigint to money
> > > LINE 1: select 871234872319489323::money;
> > > ^
> > > test=> select 871234872::money;
> > > ERROR: cannot cast type integer to money
> > > LINE 1: select 871234872::money;
> > > ^
> > > test=> select 87123487231.3::money;
> > > ERROR: cannot cast type numeric to money
> > > LINE 1: select 87123487231.3::money;
> > > ^
> >
> > I agree. I wasn't the one that added the meta information.
>
> OK, so the big question then is if we are un-depricating this data type,
> does it have the behavior we want? (And the regression addition will be
> helpful too.)
Added to TODO list:
* Allow MONEY to be cast to/from other numeric data types
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> Added to TODO list:
> * Allow MONEY to be cast to/from other numeric data types
So in other words, that's been added to the TODO list *purely* on your
own say-so, and not because any users asked for it or anyone else thinks
it's a good idea.
Since MONEY really ought to be considered a tagged type, I'm not at all
impressed with the idea that there should be default casts between it
and plain numerics. There's a fundamental semantic gap there.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Mon, 26 Nov 2007 21:19:48 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Added to TODO list:
> > * Allow MONEY to be cast to/from other numeric data types
>
> So in other words, that's been added to the TODO list *purely* on your
> own say-so, and not because any users asked for it or anyone else
> thinks it's a good idea.
>
Well if we are going to continue to support money (which I am against)
we should support the casting to numeric as that is by far a more
common implementation of money and we will have mixed environments.
> Since MONEY really ought to be considered a tagged type, I'm not at
> all impressed with the idea that there should be default casts
> between it and plain numerics. There's a fundamental semantic gap
> there.
Perhaps but the practical gap is much narrower.
Sincerely,
Joshua D. Drake
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 2: Don't 'kill -9' the
> postmaster
>
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHS4QhATb/zqfZUUQRAraiAJ9HLeqG7nRbblPvJhu/JQwhOrmzxQCgpWGE
1JShnZ4xwM1+lQzTKCkGVcw=
=sErU
-----END PGP SIGNATURE-----
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Well if we are going to continue to support money (which I am against)
> we should support the casting to numeric as that is by far a more
> common implementation of money and we will have mixed environments.
So, you don't use MONEY, and you don't want to, but nonetheless you
know better than the people who do use MONEY what they need.
Aside from the semantic-gap issue, there is the point that providing
a cast might actually mask application errors. I can well imagine
cases where one of the reasons for using MONEY is *exactly* that it's
not a plain number or easily convertible to one.
regards, tom lane
Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
> > Well if we are going to continue to support money (which I am against)
> > we should support the casting to numeric as that is by far a more
> > common implementation of money and we will have mixed environments.
>
> So, you don't use MONEY, and you don't want to, but nonetheless you
> know better than the people who do use MONEY what they need.
I found out you can cast numerics to MONEY by using two casts:
test=> SELECT 12321.12::text::money; money------------ $12,321.12(1 row)
For some reason this doesn't work in 8.2 but does in 8.3. The reverse
doesn't work:
test=> SELECT '12321.12'::money::text::numeric;ERROR: invalid input syntax for type numeric: "$12,321.12"
The big problem is that MONEY is a string so the dollar sign and commas
are a problem.
> Aside from the semantic-gap issue, there is the point that providing
> a cast might actually mask application errors. I can well imagine
> cases where one of the reasons for using MONEY is *exactly* that it's
> not a plain number or easily convertible to one.
Perhaps all we need is a way to accomplish the casting so it isn't
automatic. This works:
test=> SELECT regexp_replace('2343'::money::text, '[^$,]*', '', 'g')::numeric; regexp_replace----------------
2343.00(1row)
but the '$' and ',' are locale-specific and I can't think of a way to do
this generically.
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Mon, 26 Nov 2007 23:47:04 -0500 (EST)
Bruce Momjian <bruce@momjian.us> wrote:
> Perhaps all we need is a way to accomplish the casting so it isn't
> automatic. This works:
>
> test=> SELECT regexp_replace('2343'::money::text, '[^$,]*',
> '', 'g')::numeric; regexp_replace
> ----------------
> 2343.00
> (1 row)
>
> but the '$' and ',' are locale-specific and I can't think of a way to
> do this generically.
With a regex?
Joshua D. Drake
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHS6RjATb/zqfZUUQRAtZyAJ9VZeCzjX+RSGr3A9eWXTcbHwnc4gCgoZcH
LcO9KnjcycRo5YjCektSJXg=
=HZpl
-----END PGP SIGNATURE-----
Joshua D. Drake wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Mon, 26 Nov 2007 23:47:04 -0500 (EST)
> Bruce Momjian <bruce@momjian.us> wrote:
>
> > Perhaps all we need is a way to accomplish the casting so it isn't
> > automatic. This works:
> >
> > test=> SELECT regexp_replace('2343'::money::text, '[^$,]*',
> > '', 'g')::numeric; regexp_replace
> > ----------------
> > 2343.00
> > (1 row)
> >
> > but the '$' and ',' are locale-specific and I can't think of a way to
> > do this generically.
>
> With a regex?
The problem is there is no locale-independent way to determine if
'123.456' is ~123k or ~123. I think we are going to need a way to
output the MONEY value without a currency and thousands symbols.
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Tom Lane wrote: > Aside from the semantic-gap issue, there is the point that providing > a cast might actually mask application errors. I can well imagine > cases where one of the reasons for using MONEY is *exactly* that it's > not a plain number or easily convertible to one. I'm always against casts, but I can hardly object to an explicit cast between money and numeric. -- Peter Eisentraut http://developer.postgresql.org/~petere/