Tuesday, April 11, 2006

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...

Labels:

4 Comments:

Anonymous Anonymous said...

alter table x alter column c type numeric(10,2) using numeric(10,2) '0.00';

denny crain.

21 August, 2006 13:10  
Anonymous Milivoj said...

The "alter table ..." method suggested by Denny doesn't seem to work in PostgreSQL 7.4 (the version currently in Debian stable). But if it does in later versions, that's good to know.

25 August, 2006 12:26  
Anonymous Rick van Hattem said...

You could also do it automatically with a cast, that saves you the extra column.

This should work:
CREATE CAST (money AS numeric) WITH FUNCTION money2numeric(money) AS assignment;

Do note that you can easily loose details like this, if you have a number like this 12.34 and you convert it to a numeric(10,0) then the new value will be 12. The 0.34 will be lost instantly.

15 January, 2007 01:39  
Anonymous pathfinder said...

Thank you for great time-saving hints contained in both post and comments, regards.

28 March, 2008 12:16  

Post a Comment

Links to this post:

Create a Link

<< Home