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@xxxxxxxxxxx
