Re: Extracting data from deprecated MONEY fields

Thanks Adrian ~ See comments at end.

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Adrian Klaver
> Sent: Friday, June 06, 2008 11:49 AM
> To: pgsql-general(at)postgresql(dot)org
> Cc: Ken Winter
> Subject: Re: [GENERAL] 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.
...
> **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;
>
I'm indeed trying to get rid of US$ notation.

Alas, here's what I get from trying the regexp pattern on my "amount" column
(type=money):

regexp_replace(amount::money::text, '[$,]', '', 'g')::numeric -> "cannot
cast type money to text"

regexp_replace(amount::text, '[$,]', '', 'g')::numeric -> "cannot cast type
money to text"

And if remove the cast to text, I get:

regexp_replace(amount::money, '[$,]', '', 'g')::numeric -> " function
regexp_replace(money, "unknown", "unknown", "unknown") does not exist"

~ Ken