How to convert "money" columns to "numeric"?
От | Ken Winter |
---|---|
Тема | How to convert "money" columns to "numeric"? |
Дата | |
Msg-id | 002e01c72dee$36d9d3a0$6503a8c0@KenIBM обсуждение исходный текст |
Ответы |
Re: How to convert "money" columns to "numeric"?
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: