I want to convert a column named "amount", currently of type money, to type numeric(10,2). When I try to do this using: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2); I get: PostgreSQL Error Code: (1) ERROR: column "amount" cannot be cast to type "pg_catalog.numeric" So then I figure I need to do it with SQL of the form: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2) USING <expression>; But I can't find a conversion function or operator that will accept a "money" column as input. For example: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2) USING to_number(amount, '99999999.99'); Evokes this error message: PostgreSQL Error Code: (1) ERROR: function to_number(money, "unknown") does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. And I can't seem to cast a "money" column into anything else. For example: ALTER TABLE transaction ALTER COLUMN amount TYPE NUMERIC(10,2) USING cast(amount as numeric); Evokes: PostgreSQL Error Code: (1) ERROR: column "amount" cannot be cast to type "pg_catalog.numeric" So I'm fresh out of ideas - other than dropping and recreating the column, which would lose a lot of data. ~ TIA ~ Ken
