Re: Extracting data from deprecated MONEY fields

On Friday 06 June 2008 8:49 am, Adrian Klaver wrote:
> 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;
>

Oops this only works with 8.3, I forgot you where working with 8.0. I tried
the dump and restore that Josh suggested and that did not work as it choked
on the $ sign. The only way I could get it to work was to remove the $ from
the dumped data.

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