Обсуждение: 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/