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:
- Connect to your database
#psql -U postgres my_database
- Add the plperl language
CREATE TRUSTED PROCEDURAL LANGUAGE 'plperl' HANDLER plperl_call_handler;
- Create the function
CREATE OR REPLACE FUNCTION money2numeric(money)(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)
RETURNS "numeric" AS
'$_=shift; s/[^0-9\.\-]//g; return 1 * $_;'
LANGUAGE 'plperl' IMMUTABLE STRICT;
- Add a numeric column
ALTER TABLE my_table ADD COLUMN my_new_col numeric(15,2);
- Update your data
UPDATE my_table SET my_new_col = money2numeric(my_old_col);
- Drop the old column
ALTER TABLE my_table DROP COLUMN my_old_col;
- Rename the new column to the old name
ALTER TABLE my_table RENAME COLUMN my_old_col TO my_new_col;