Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
What version of postgres are you running? –  Jack Douglas Sep 28 '11 at 9:31
add comment

3 Answers 3

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

share|improve this answer
add comment

Try

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

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

share|improve this answer
add comment

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: " ###.##"
share|improve this answer
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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