5

I am attempting to convert a PostgreSQL table column from integer to money, but I am receiving the error:

cannot cast type MyColumn to money

I have tried these two statements, but just haven't found any real example on how to really do it.

ALTER TABLE products 
    ALTER COLUMN price TYPE money

and:

ALTER TABLE products 
    ALTER COLUMN price TYPE money USING to_char(price, '999.99')

How can you change the datatype of a PostgreSQL column from integer to money?

7

Just try the following command.

ALTER TABLE products ALTER COLUMN price TYPE money using price::text::money

Non-quoted numeric values can be converted to money by casting the numeric value to text and then money

4

Try

ALTER TABLE products 
    ALTER COLUMN price TYPE money USING price::money

btw what error did you get on your second try up there?

4

As francs already implied, there is no default cast from integer to money (until 9.1), instead you need an intermediate cast to text:

select 78::integer::money;
ERROR:  cannot cast type integer to money
LINE 1: select 78::integer::money;
                          ^
select 78::integer::text::money;
 money
--------
 £78.00

Your error is subtle however. Note that while select to_char(78, '999.99')::money succeeds, the following fails:

alter table products alter column price type money using to_char(price, '999.99');
ERROR:  column "price" cannot be cast to type money

but with an explicit cast it succeeds:

alter table products alter column price type money using to_char(price, '999.99')::money;
ALTER TABLE

this looks like a bug to me - I do not have 9.0 or 9.1 to test on yet, hopefully someone else can't say if it is fixed in those versions or if we should raise it as a bug.


aside: your original will of course fail even with an explicit cast if there are any rows where price>=1000:

alter table products alter column price type money using to_char(price, '999.99')::money;
ERROR:  invalid input syntax for type money: " ###.##"
  • 1
    hehe, and here I was gonna suggest inserting a new column, updating into via convert, then deleting the old one (but see I think like a programmer :P ) – jcolebrand Sep 27 '11 at 18:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.