From: | "Ken Winter" <ken(at)sunward(dot)org> |
---|---|
To: | "PostgreSQL pg-general List" <pgsql-general(at)postgresql(dot)org> |
Cc: | "'Ken Winter'" <ken(at)sunward(dot)org>, "Jim Myers" <myersjj(at)pacbell(dot)net> |
Subject: | How to convert "money" columns to "numeric"? |
Date: | 2007-01-01 21:45:52 |
Message-ID: | 002e01c72dee$36d9d3a0$6503a8c0@KenIBM |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | brian | 2007-01-01 21:47:37 | Re: spooky refusal to insert [SOLVED] |
Previous Message | Ragnar | 2007-01-01 21:33:30 | Re: Problem with index in OR'd expression |