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
