Re: [HACKERS] [PATCHES] Fixes for MONEY type using locale
Bruce Momjian wrote: D'Arcy J.M. Cain wrote: On Sat, 24 Nov 2007 11:27:38 -0500 (EST) Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Fixes for MONEY type using locale
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Fixes for MONEY type using locale
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 26 Nov 2007 21:19:48 -0500 Tom Lane [EMAIL PROTECTED] wrote: Bruce Momjian [EMAIL PROTECTED] 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- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [PATCHES] Fixes for MONEY type using locale
Joshua D. Drake [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Fixes for MONEY type using locale
Tom Lane wrote:
Joshua D. Drake [EMAIL PROTECTED] 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
(1 row)
but the '$' and ',' are locale-specific and I can't think of a way to do
this generically.
--
Bruce Momjian [EMAIL PROTECTED]http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Fixes for MONEY type using locale
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Mon, 26 Nov 2007 23:47:04 -0500 (EST)
Bruce Momjian [EMAIL PROTECTED] 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-
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] [PATCHES] Fixes for MONEY type using locale
Joshua D. Drake wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Mon, 26 Nov 2007 23:47:04 -0500 (EST)
Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED]http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] [PATCHES] Fixes for MONEY type using locale
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/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
