Thread: How to convert "money" columns to "numeric"?

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

On Monday 01 January 2007 1:45 pm, Ken Winter wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
Take a look at the GeneralBits column below for a possible solution(see
heading Convert money type to numeric)-
http://www.varlena.com/GeneralBits/75.php
--
Adrian Klaver
aklaver@comcast.net