cannot cast type money

In PostgreSQL, there used to be a type money which is now deprecated. It is suggested to replace it with numeric(p,s), but how do you convert your existing money columns?

Someone complained that no migration path is provided, but that conversation ended up in some useless nitpicking about whether it had ever been possible in some previous version.

There is a MONEY Conversion Module patch, but that requires you to recompile PostgreSQL and will only work with some specific versions of Postgres.

Well, if you inadvertently used that stupid money type and need to convert, maybe the simple solution below will work for you. It takes advantage of the fact that you can use Perl in PostgreSQL functions.

The following steps have worked for me:

1. Connect to your database
   #psql -U postgres my_database


2. Add the plperl language
   CREATE TRUSTED PROCEDURAL LANGUAGE 'plperl' HANDLER plperl_call_handler;


3. Create the function
   

   CREATE OR REPLACE FUNCTION money2numeric(money)
   RETURNS "numeric" AS
   '$_=shift; s/[^0-9\.\-]//g; return 1 * $_;'
   LANGUAGE 'plperl' IMMUTABLE STRICT;

   (in case you are not familiar with Perl, here is what it does: assign the argument to the $_ variable; remove any characters which are not a digit, a dot or a minus sign; return the result multiplied by 1 to ensure it is numeric)


4. Add a numeric column
   ALTER TABLE my_table ADD COLUMN my_new_col numeric(15,2);


5. Update your data
   UPDATE my_table SET my_new_col = money2numeric(my_old_col);


6. Drop the old column
   ALTER TABLE my_table DROP COLUMN my_old_col;


7. Rename the new column to the old name
   ALTER TABLE my_table RENAME COLUMN my_old_col TO my_new_col;

That's it...