Postgresql: Converting money type to numeric!

Quick post here, as I was having trouble finding any information(that worked, or indepth enough to work for what I needed).

The type money is now deprecated from postgresql, and a few of our tables contained this data type. This is fine, and worked as we wanted, until there came a time I needed to do a query such as:

select * from mytable where (mytable.charge > 0);

seems easy enough, but if myfieldname is of type money, you will get nice errors such as:

ERROR: operator does not exist: money > integer
HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.

Doing what the Hint suggests isnt easy, and theres pretty much no good way to cast money to anything.

A few examples on the net dont work, depending on version, or can mess up the data.

After a bit of searching and messing, ive found a solution that does whats needed:

First you need to do:

set lc_monetary=’C’;

to basically control the currency, ours was set to £ but doing that sets it back to $ and makes things easier to work with.

Next you need to do:

alter table yourtable alter column yourfield type numeric(16,2) using translate(textin(cash_out(yourfield)), ‘$,’, ”)::numeric;

Once thats done, you’re set and it seems to work flawlessley (in version: 8.1.9 anyway).

May be a good idea to make a copy of the table before, just incase:

create table backup_table_yourtable AS select * from yourtable;

then if you have any issues you can revert back!