RE: [HACKERS] money data type and conversions

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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>

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(at)candle(dot)pha(dot)pa(dot)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?

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(at)candle(dot)pha(dot)pa(dot)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(at)michvhf(dot)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(at){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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

> > > 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(at)alumni(dot)caltech(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

> What about the printing of currency symbol?

Won't be missed, at least for anyone writing to SQL92 ;)

- Thomas

--
Thomas Lockhart lockhart(at)alumni(dot)caltech(dot)edu
South Pasadena, California

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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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(at){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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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(at)pacifier(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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(at){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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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(at){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.