Обсуждение: money data type and conversions
Can someone explain why our money type in 6.5 requires quotes, and why
there is no int() function for it?
---------------------------------------------------------------------------
test=> create table t(x money);
CREATE
test=> insert into t values (3.3);
ERROR: Attribute 'x' is of type 'money' but expression is of type 'float8' You will need to rewrite or cast the
expression
test=> insert into t values (3.33);
ERROR: Attribute 'x' is of type 'money' but expression is of type 'float8' You will need to rewrite or cast the
expression
test=> insert into t values (money(3.33));
ERROR: No such function 'money' with the specified attributes
test=> insert into t values (cash(3.33));
ERROR: No such function 'cash' with the specified attributes
test=> insert into t values (3.33);
ERROR: Attribute 'x' is of type 'money' but expression is of type 'float8' You will need to rewrite or cast the
expression
test=> insert into t values ('3.33');
INSERT 18569 1
test=> select int(x) from t;
ERROR: No such function 'int' with the specified attributes
test=> select int4(x) from t;
ERROR: No such function 'int4' with the specified attributes
-- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
Well, can't explain the why's...
But I have the code to add to cash.c and cash.h to add the conversion
functions, but still have to figure out how to get PostgreSQL to recognize
it... Guessing... it's in fmgrtab.c right?
Duane
> Can someone explain why our money type in 6.5 requires quotes, and why
> there is no int() function for it?
>
> ---------------------------------------------------------------------------
>
>
> test=> create table t(x money);
> CREATE
> test=> insert into t values (3.3);
> ERROR: Attribute 'x' is of type 'money' but expression is of type 'float8'
> You will need to rewrite or cast the expression
> test=> insert into t values (3.33);
> ERROR: Attribute 'x' is of type 'money' but expression is of type 'float8'
> You will need to rewrite or cast the expression
> test=> insert into t values (money(3.33));
> ERROR: No such function 'money' with the specified attributes
> test=> insert into t values (cash(3.33));
> ERROR: No such function 'cash' with the specified attributes
> test=> insert into t values (3.33);
> ERROR: Attribute 'x' is of type 'money' but expression is of type 'float8'
> You will need to rewrite or cast the expression
> test=> insert into t values ('3.33');
> INSERT 18569 1
> test=> select int(x) from t;
> ERROR: No such function 'int' with the specified attributes
> test=> select int4(x) from t;
> ERROR: No such function 'int4' with the specified attributes
>
> --
> Bruce Momjian | http://www.op.net/~candle
> maillist@candle.pha.pa.us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>
On 22-Jun-99 Bruce Momjian wrote:
> Can someone explain why our money type in 6.5 requires quotes, and why
> there is no int() function for it?
Dunno about the int() stuff, but it seems that I've always had to quote
money. I ass-u-me d it had to do with the $ sign, 'cuze using a float
would cause it to crab about the wrong data type.
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com flame-mail: /dev/null # include <std/disclaimers.h>
TEAM-OS2 Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore
http://www.cloudninegifts.com
==========================================================================
Thus spake Bruce Momjian
> Can someone explain why our money type in 6.5 requires quotes, and why
> there is no int() function for it?
Good question. I wonder if #2 is the answer to #1.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
> Thus spake Bruce Momjian > > Can someone explain why our money type in 6.5 requires quotes, and why > > there is no int() function for it? > > Good question. I wonder if #2 is the answer to #1. > Added to TODO: * Money type requires quotes for input, and no coversion functions -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce, TODO almost done: I have three files which implement two functions to convert from money to integer and from integer to money. Tested it out... works Who should I send these to to have the changes applied to a later release? Thanx, Duane > > Thus spake Bruce Momjian > > > Can someone explain why our money type in 6.5 requires quotes, and why > > > there is no int() function for it? > > > > Good question. I wonder if #2 is the answer to #1. > > > > Added to TODO: > > * Money type requires quotes for input, and no coversion functions > > -- > Bruce Momjian | http://www.op.net/~candle > maillist@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 >
> Bruce, > > TODO almost done: > > I have three files which implement two functions to convert from > money to integer and from integer to money. Tested it out... works > > Who should I send these to to have the changes applied to a later release? Send them over to the patches list. We will apply them to 6.6 because they will require a dump/restore. Thomas will probably do something with them and binary compatible types. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> > > Can someone explain why our money type in 6.5 requires quotes, and why
> > > there is no int() function for it?
> > Good question. I wonder if #2 is the answer to #1.
> Added to TODO:
> * Money type requires quotes for input, and no coversion functions
And while you are at it, add one more entry:
* Remove money type
NUMERIC and DECIMAL are (or should be, if there are rough edges since
they are so new) are the SQL92-way to represent currency. And, they
are compatible with all different conventions, since you can set the
decimal place and size of the fractional part as you want.
We didn't remove the money type for v6.5 since the newer types are so,
uh, new. But if there are no reported, unfixable problems we should
drop the money type for the next release.
As a sop to make the conversion easier, we can equivalence "money" to
"numeric(xx,2)" at that time.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
> > > > Can someone explain why our money type in 6.5 requires quotes, and why > > > > there is no int() function for it? > > > Good question. I wonder if #2 is the answer to #1. > > Added to TODO: > > * Money type requires quotes for input, and no coversion functions > > And while you are at it, add one more entry: > > * Remove money type Added to TODO: * Remove Money type and make synonym for decimal(x,2) What about the printing of currency symbol? -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
For people wondering what BeOS is: http://www.be.com/aboutbe/index.html Seems it is an OS developed for digital media and network appliances. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Thus spake Bruce Momjian
> * Remove Money type and make synonym for decimal(x,2)
>
> What about the printing of currency symbol?
That's the one thing that the new types don't offer but that was often
problematical anyway. In fact, I even submitted a patch to cash.c to
remove the currency symbol based on earlier discussions. The only
reason it wasn't added was that the type was supposed to be removed
soon anyway. Perhaps we should apply the patch anyway for now until
it is removed.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
> Thus spake Bruce Momjian > > * Remove Money type and make synonym for decimal(x,2) > > > > What about the printing of currency symbol? > > That's the one thing that the new types don't offer but that was often > problematical anyway. In fact, I even submitted a patch to cash.c to > remove the currency symbol based on earlier discussions. The only > reason it wasn't added was that the type was supposed to be removed > soon anyway. Perhaps we should apply the patch anyway for now until > it is removed. > Not good to change behavour in a minor release if we can help it. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> What about the printing of currency symbol?
Won't be missed, at least for anyone writing to SQL92 ;)
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
At 01:52 AM 6/23/99 +0000, Thomas Lockhart wrote: >NUMERIC and DECIMAL are (or should be, if there are rough edges since >they are so new) are the SQL92-way to represent currency. And, they >are compatible with all different conventions, since you can set the >decimal place and size of the fractional part as you want. This is an excellent point. The portable and standard numeric and decimal types are the way to go. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, and other goodies at http://donb.photo.net
> Well, can't explain the why's... > > But I have the code to add to cash.c and cash.h to add the conversion > functions, but still have to figure out how to get PostgreSQL to recognize > it... Guessing... it's in fmgrtab.c right? > Duane, sonds like people want to remove the Money/cash type and transfer everyone over to decimal which has full precision and is much better for currency. Sorry. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Thus spake Bruce Momjian
> > Well, can't explain the why's...
> >
> > But I have the code to add to cash.c and cash.h to add the conversion
> > functions, but still have to figure out how to get PostgreSQL to recognize
> > it... Guessing... it's in fmgrtab.c right?
> >
>
> Duane, sonds like people want to remove the Money/cash type and transfer
> everyone over to decimal which has full precision and is much better for
> currency.
Is there any reason why we don't just leave money in? I know that NUMERIC
and DECIMAL will handle money amounts but the money type does a few
extra things related to locale, even if we remove the currency symbol
and perhaps we should leave that in if people are expected to use the
new types. It also determines whether the comma or period is the correct
separator, puts separators in the correct place and determines where the
decimal point goes. Also, check out what the following does.
select cash_words_out('157.23');
Althugh there appears to be a bug in that function that chops the last
character from the output.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
> Thus spake Bruce Momjian
> > > Well, can't explain the why's...
> > >
> > > But I have the code to add to cash.c and cash.h to add the conversion
> > > functions, but still have to figure out how to get PostgreSQL to recognize
> > > it... Guessing... it's in fmgrtab.c right?
> > >
> >
> > Duane, sonds like people want to remove the Money/cash type and transfer
> > everyone over to decimal which has full precision and is much better for
> > currency.
>
> Is there any reason why we don't just leave money in? I know that NUMERIC
> and DECIMAL will handle money amounts but the money type does a few
> extra things related to locale, even if we remove the currency symbol
> and perhaps we should leave that in if people are expected to use the
> new types. It also determines whether the comma or period is the correct
> separator, puts separators in the correct place and determines where the
> decimal point goes. Also, check out what the following does.
>
> select cash_words_out('157.23');
>
> Althugh there appears to be a bug in that function that chops the last
> character from the output.
Maybe we will have to add '$' symbols to a special case of the numeric
type, or add a function to output numeric in money format?
-- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
Thus spake Bruce Momjian
> Maybe we will have to add '$' symbols to a special case of the numeric
> type, or add a function to output numeric in money format?
That's another thought I had. However, it isn't the '$' symbol. The
idea is that it takes the symbol from the current locale. That's what
makes handling the information so hard, you don't know how many characters
are used by the currency symbol.
However, cash_out and cash_words_out can probably be dropped into the
decimal code. There should be some small changes though. In particular
the money type moves the decimal point to a position in a fixed string
of digits but for decimal it should honour the type's positioning.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> Maybe we will have to add '$' symbols to a special case of the numeric
> type, or add a function to output numeric in money format?
I like the last idea (add a formatting function), because it's simple,
self-contained, and doesn't force any solutions on anyone. Don't want
any decoration on your number? Just read it out. Don't like the
decoration added by the formatting function? Write your own function.
No table reconstruction required. With a data-type-driven approach,
changing your mind is painful because you have to rebuild your tables.
We'd probably also want an inverse function that would strip off the
decoration and produce a numeric, but that's easy too...
regards, tom lane
> Bruce Momjian <maillist@candle.pha.pa.us> writes: > > Maybe we will have to add '$' symbols to a special case of the numeric > > type, or add a function to output numeric in money format? > > I like the last idea (add a formatting function), because it's simple, > self-contained, and doesn't force any solutions on anyone. Don't want > any decoration on your number? Just read it out. Don't like the > decoration added by the formatting function? Write your own function. > No table reconstruction required. With a data-type-driven approach, > changing your mind is painful because you have to rebuild your tables. > > We'd probably also want an inverse function that would strip off the > decoration and produce a numeric, but that's easy too... Added to TODO: * Remove Money type, add money formatting for decimal type I should add I have reorganized the TODO list to be clearer. People may want to check it out on our newly designed web site. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026