Re: Extracting data from deprecated MONEY fields

On Friday 06 June 2008 8:25 am, Ken Winter wrote:
> Thanks, Joshua ~
>
> What you suggest is basically what I'm trying to do. Where I'm stuck is in
> finding a construct (a CAST or whatever) to turn the existing "money"
> column data (directly or indirectly) into numeric. I've tried to convert a
> column named "amount" in the following ways, with the following results:
>
> CAST(amount AS numeric) -> "cannot cast type money to numeric"
> CAST(amount AS numeric(10,2)) -> "cannot cast type money to numeric"
> CAST(amount AS decimal) -> "cannot cast type money to numeric"
> CAST(amount AS text) -> "cannot cast type money to text"
> CAST(amount AS varchar) -> "cannot cast type money to character varying"
> to_char(money) -> "function to_char(money) does not exist"

I don't know if this helps.
From docs;
http://www.postgresql.org/docs/8.3/interactive/datatype-money.html
The money type stores a currency amount with a fixed fractional precision;
see Table 8-3. Input is accepted in a variety of formats, including integer
and floating-point literals, as well as "typical" currency formatting, such
as '$1,000.00'. Output is generally in the latter form but depends on the
locale. Non-quoted numeric values can be converted to money by casting the
numeric value to text and then money:

SELECT 1234::text::money;

**There is no simple way of doing the reverse in a locale-independent manner,
namely casting a money value to a numeric type. If you know the currency
symbol and thousands separator you can use regexp_replace():

SELECT regexp_replace('52093.89'::money::text, '[$,]', '', 'g')::numeric;

>
> ~ Ken
>
> > -----Original Message-----
> > From: Joshua D. Drake [mailto:jd(at)commandprompt(dot)com]
> > Sent: Thursday, June 05, 2008 11:22 PM
> > To: Ken Winter
> > Cc: PostgreSQL pg-general List
> > Subject: Re: [GENERAL] Extracting data from deprecated MONEY fields
> >
> > Ken Winter wrote:
> > > I understand from
> > > http://www.postgresql.org/docs/8.0/static/datatype-money.html that the
> > > "money" data type is deprecated.
> >
> > Money is no longer deprecated in newer releases (specifically 8.3),
> > although I do think it would be wise to push it to numeric.
> >
> > I think the way to do it would be to backup the table and edit the table
> > definition from the file. Make the money a numeric. Then reload the
> > table from the backup.
> >
> > Sincerely,
> >
> > Joshua D. Drake

--
Adrian Klaver
aklaver(at)comcast(dot)net