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

Question

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?

Answer 1

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

Answer 2

Try

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

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

Answer 3

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: " ###.##"