POSTGRES - Change money field to Integer....

So, we had a table with a money field when all of a sudden we realised that it would make a lot more sense for the amount to be expressed in cents instead of in dollars.

$ 1234.56 would now become 123456

I am sure that there is a far better way to do this but - I am an occasional DB admin and so I figured that I needed to

1. Convert dollars to cents
2. Convert Money to Integer

#1 was easy : update bills set amount=amount*10;

#2 was a bit more tricky. Very hard to convert Money to Int.

amount::varchar does, however, yield a string so

Create a new int column:

Alter table bills add column iamount int;

Then copy the correct amount into it:

update bills set iamount =
     replace(substr(amount::varchar,2,length(amount::varchar)-4),
     ',','')::int;

Then drop the old column and rename the new....

alter table bills drop column amount;

alter table bills rename column iamount to amount;