No migration path for MONEY

Folks,

Bug reported off IRC:

MONEY Type cannot be cast to any other type, preventing migration from this
depreciated data type.

Affects: 7.2.3, 7.3.1
Frequency: 100% Reproducable
Effect When Occurring: Unable to convert, query data
Difficulty of Fix: Easy, probably
Certianty of Diagnosis: 100%

On both 7.2.3 and 7.3.1 all of the following statements will fail:

select cast('40.00'::MONEY as NUMERIC);
select cast('40.00'::MONEY as DOUBLE);
select cast('40.00'::MONEY as VARCHAR);
select "numeric"('40.00'::MONEY);
select to_char('40.00'::MONEY, '99999999999.9999');

This means that someone who has inherited or upgarded a 6.5 database with
MONEY columns has no way to migrate them to NUMERIC columns other than an
external language script or dump and reload from COPY file.

I propose that we need to restore the CAST(MONEY AS NUMERIC) function so that
users can migrate old databases to the new data type. In later versions of
postgres, I suggest that MONEY be abandoned as a true data type and instead
become a DOMAIN of NUMERIC for those converting.

--
-Josh Berkus
Aglio Database Solutions
San Francisco

They are probably better off just changing the column data type, _and_
we need someone to get MONEY working as an extented NUMERIC type.

---------------------------------------------------------------------------

Josh Berkus wrote:
> Folks,
>
> Bug reported off IRC:
>
> MONEY Type cannot be cast to any other type, preventing migration from this
> depreciated data type.
>
> Affects: 7.2.3, 7.3.1
> Frequency: 100% Reproducable
> Effect When Occurring: Unable to convert, query data
> Difficulty of Fix: Easy, probably
> Certianty of Diagnosis: 100%
>
> On both 7.2.3 and 7.3.1 all of the following statements will fail:
>
> select cast('40.00'::MONEY as NUMERIC);
> select cast('40.00'::MONEY as DOUBLE);
> select cast('40.00'::MONEY as VARCHAR);
> select "numeric"('40.00'::MONEY);
> select to_char('40.00'::MONEY, '99999999999.9999');
>
> This means that someone who has inherited or upgarded a 6.5 database with
> MONEY columns has no way to migrate them to NUMERIC columns other than an
> external language script or dump and reload from COPY file.
>
> I propose that we need to restore the CAST(MONEY AS NUMERIC) function so that
> users can migrate old databases to the new data type. In later versions of
> postgres, I suggest that MONEY be abandoned as a true data type and instead
> become a DOMAIN of NUMERIC for those converting.
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce,

> They are probably better off just changing the column data type, _and_
> we need someone to get MONEY working as an extented NUMERIC type.

Apparently D'Arcy McCain is going to do this. Go, D'arcy!

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Bruce Momjian writes:

> we need someone to get MONEY working as an extented NUMERIC type.

How would the new "money" be different from "numeric"? If we have
"money", should we have "length", "mass", and "temperature"? I think not.
Just let the money type die and that's it.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> How would the new "money" be different from "numeric"?

[ temporarily re-dons currency-trader hat... ]

What would actually be useful is a money type that carries along an
indication of which currency the amount is expressed in (not per-column
as Bruce naively suggested, but right in the datum). This would allow
conversions to be performed between different currencies, as well as
allowing the correct decoration to be provided on output.

> If we have "money", should we have "length", "mass", and
> "temperature"? I think not.

Physicists have found use for numeric objects that carry along an
indication of the units they're in --- in other words, not "length" etc,
but a generic "measurement" type that might tag its value as "meters" or
"kilograms" or "furlongs per fortnight".

I'm not eager to go and write such a datatype right now, but if someone
wanted to do the work I'd be in favor of adopting one.

regards, tom lane

On Tue, Jan 28, 2003 at 08:24:13PM +0100, Peter Eisentraut wrote:
> Bruce Momjian writes:
>
> > we need someone to get MONEY working as an extented NUMERIC type.
>
> How would the new "money" be different from "numeric"? If we have
> "money", should we have "length", "mass", and "temperature"? I think not.
> Just let the money type die and that's it.

Agree. BTW, the correct "money" implementation is very depend on
locales, for example the current implementation is not usable in my
country and our currency. It must be very powerful, an example like
strfmon() in libc. I think numeric and to_char() is useful already
now.

Karel

--
Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

The problem here is not that we are getting rid of MONEY as datatype, (I see
no need for it with having numeric) the problem comes from haveing no way to
migrate MONEY to NUMERIC short of hand editing the pgdump file and then
reimporting. Yes I know I can use sed, but that is not the point. In 7.1
and prior I could do a SELECT '1.0'::MONEY::TEXT or to NUMERIC or to FLOAT
even.

On Monday 27 January 2003 12:06, Bruce Momjian wrote:
> They are probably better off just changing the column data type, _and_
> we need someone to get MONEY working as an extented NUMERIC type.
>
> ---------------------------------------------------------------------------
>
> Josh Berkus wrote:
> > Folks,
> >
> > Bug reported off IRC:
> >
> > MONEY Type cannot be cast to any other type, preventing migration from
> > this depreciated data type.
> >
> > Affects: 7.2.3, 7.3.1
> > Frequency: 100% Reproducable
> > Effect When Occurring: Unable to convert, query data
> > Difficulty of Fix: Easy, probably
> > Certianty of Diagnosis: 100%
> >
> > On both 7.2.3 and 7.3.1 all of the following statements will fail:
> >
> > select cast('40.00'::MONEY as NUMERIC);
> > select cast('40.00'::MONEY as DOUBLE);
> > select cast('40.00'::MONEY as VARCHAR);
> > select "numeric"('40.00'::MONEY);
> > select to_char('40.00'::MONEY, '99999999999.9999');
> >
> > This means that someone who has inherited or upgarded a 6.5 database with
> > MONEY columns has no way to migrate them to NUMERIC columns other than an
> > external language script or dump and reload from COPY file.
> >
> > I propose that we need to restore the CAST(MONEY AS NUMERIC) function so
> > that users can migrate old databases to the new data type. In later
> > versions of postgres, I suggest that MONEY be abandoned as a true data
> > type and instead become a DOMAIN of NUMERIC for those converting.
> >
> > --
> > -Josh Berkus
> > Aglio Database Solutions
> > San Francisco
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org

--
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx: 250.763.1759
http://www.wavefire.com

Darcy Buskermolen <darcy(at)wavefire(dot)com> writes:
> In 7.1 and prior I could do a SELECT '1.0'::MONEY::TEXT or to NUMERIC
> or to FLOAT even.

No, you couldn't.

regression=# SELECT '1.0'::MONEY::TEXT ;
ERROR: Cannot cast type 'money' to 'text'
regression=# select version();
version
------------------------------------------------------------------
PostgreSQL 7.1.3 on hppa2.0-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)

7.0 is the same. 7.2 is the same. 7.3 is the same except it doesn't
put quotes in the error message ;-)

It might be worth adding some conversion functions in the future ---
but don't assert that we've removed such, because we have not.

regards, tom lane

Sorry my mistake on versions.

darcy=> SELECT '1.0'::MONEY::FLOAT;
?column?
--------
1
(1 row)

darcy=> SELECT '1.0'::MONEY::TEXT;
?column?
--------
1.0
(1 row)

darcy=> select version();
version
--------------------------------------------------------------------
PostgreSQL 6.5.2 on i386-unknown-freebsd3.2, compiled by gcc 2.7.2.1
(1 row)

On Wednesday 29 January 2003 09:04, Tom Lane wrote:
> Darcy Buskermolen <darcy(at)wavefire(dot)com> writes:
> > In 7.1 and prior I could do a SELECT '1.0'::MONEY::TEXT or to NUMERIC
> > or to FLOAT even.
>
> No, you couldn't.
>
> regression=# SELECT '1.0'::MONEY::TEXT ;
> ERROR: Cannot cast type 'money' to 'text'
> regression=# select version();
> version
> ------------------------------------------------------------------
> PostgreSQL 7.1.3 on hppa2.0-hp-hpux10.20, compiled by GCC 2.95.3
> (1 row)
>
> 7.0 is the same. 7.2 is the same. 7.3 is the same except it doesn't
> put quotes in the error message ;-)
>
> It might be worth adding some conversion functions in the future ---
> but don't assert that we've removed such, because we have not.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

--
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx: 250.763.1759
http://www.wavefire.com

Darcy Buskermolen <darcy(at)wavefire(dot)com> writes:
> Sorry my mistake on versions.
> darcy=> SELECT '1.0'::MONEY::FLOAT;
> ?column?
> --------
> 1
> (1 row)
> darcy=> select version();
> version
> --------------------------------------------------------------------
> PostgreSQL 6.5.2 on i386-unknown-freebsd3.2, compiled by gcc 2.7.2.1
> (1 row)

I don't have a 6.5 system around to test anymore, but my recollection is
that back then, constructs like 'literal'::foo::bar were collapsed into
'literal'::bar --- so the above doesn't prove 6.5 could actually convert
money into float. What happens if you take a money column and try to
coerce it, ie "SELECT moneycol::float FROM mytable" ?

regards, tom lane

Tom you are correct here (like usual), sorry for the wasted thread and time
regarding functions that I remembered being there but infact were not.

On Wednesday 29 January 2003 10:56, Tom Lane wrote:
> Darcy Buskermolen <darcy(at)wavefire(dot)com> writes:
> > Sorry my mistake on versions.
> > darcy=> SELECT '1.0'::MONEY::FLOAT;
> > ?column?
> > --------
> > 1
> > (1 row)
> > darcy=> select version();
> > version
> > --------------------------------------------------------------------
> > PostgreSQL 6.5.2 on i386-unknown-freebsd3.2, compiled by gcc 2.7.2.1
> > (1 row)
>
> I don't have a 6.5 system around to test anymore, but my recollection is
> that back then, constructs like 'literal'::foo::bar were collapsed into
> 'literal'::bar --- so the above doesn't prove 6.5 could actually convert
> money into float. What happens if you take a money column and try to
> coerce it, ie "SELECT moneycol::float FROM mytable" ?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx: 250.763.1759
http://www.wavefire.com

Could we use DOMAIN to make MONEY an alias for NUMERIC, perhaps using
locale to make it customized, somehow?

---------------------------------------------------------------------------

Peter Eisentraut wrote:
> Bruce Momjian writes:
>
> > we need someone to get MONEY working as an extented NUMERIC type.
>
> How would the new "money" be different from "numeric"? If we have
> "money", should we have "length", "mass", and "temperature"? I think not.
> Just let the money type die and that's it.
>
> --
> Peter Eisentraut peter_e(at)gmx(dot)net
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073