Right you are, Tom! In case anyone else is facing the same migration, pasted in below is a pl/pgsql function that does the conversion. ~ Thanks to all ~ Ken > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Tom Lane > Sent: Saturday, June 07, 2008 11:25 AM > Subject: Re: [GENERAL] Extracting data from deprecated MONEY fields > ... > If you do it within plpgsql it should work. Just assign the money value > to a text variable (or vice versa). > > regards, tom lane CREATE OR REPLACE FUNCTION "public"."convert_money_column"(varchar, varchar, varchar) RETURNS varchar AS $BODY$ /* Converts the column given by arg 3 (in the table given by arg 2 in the schema given by arg 1) from a "money" type to a "numeric(10,2)" type, and repopulates the new column with the values from the old. Before doing that, it makes a backup of the original table, which should be deleted manually after verifying the results. */ DECLARE this_schema ALIAS FOR $1; this_table ALIAS FOR $2; this_column ALIAS FOR $3; q varchar := ''; q2 varchar := ''; rec record; this_oid oid; this_varchar varchar := ''; this_numeric numeric(10,2); n integer := 0; BEGIN q := 'CREATE TABLE ' || this_schema || '.' || this_table || '_bak' || ' AS SELECT * FROM ' || this_schema || '.' || this_table; EXECUTE q; q := 'ALTER TABLE ' || this_schema || '.' || this_table || ' ADD COLUMN ' || this_column || '_ money'; EXECUTE q; q := 'UPDATE ' || this_schema || '.' || this_table || ' SET ' || this_column || '_ = ' || this_column; EXECUTE q; q := 'ALTER TABLE ' || this_schema || '.' || this_table || ' DROP COLUMN ' || this_column || ' CASCADE'; EXECUTE q; q := 'ALTER TABLE ' || this_schema || '.' || this_table || ' ADD COLUMN ' || this_column || ' numeric(10,2)'; EXECUTE q; q := 'SELECT oid, ' || this_column || '_ AS money_column FROM ' || this_schema || '.' || this_table ; FOR rec IN EXECUTE q LOOP this_oid := rec.oid; this_varchar := rec.money_column; this_varchar := replace(this_varchar, '$', ''); this_varchar := replace(this_varchar, ',', ''); this_numeric := this_varchar; q2 := 'UPDATE ' || this_schema || '.' || this_table || ' SET ' || this_column || ' = ' || this_numeric || ' WHERE oid = ' || this_oid; EXECUTE q2; n := n + 1; END LOOP; q := 'ALTER TABLE ' || this_schema || '.' || this_table || ' DROP COLUMN ' || this_column || '_ CASCADE'; EXECUTE q; RETURN 'Did ' || n || ' records'; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;