Re: Extracting data from deprecated MONEY fields

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"
>
> ~ Ken

>> 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.

I think the steps Joshua is referring to are -

1. pg_dump -t mytable_with_money mydb > mytable_backup.sql
2. edit table definition in backup file to use numeric
3. remove $ and , from money column data
4. DROP TABLE mytable_with_money
5. psql < mytable_backup.sql

While the data is in a text file regex tasks to remove the money
formatting become a lot simpler.

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz