Mass type replacement
From PostgreSQL wiki
Occasionally one wishes to alter the type of all columns of some specified (user-defined) type to be a different type. For obvious reasons this shouldn't generally be attempted with builtin types, but for domains, enums, and composite types it may be very useful (especially for altering enum definitions prior to 9.1).
With the aid of the Dynamic DDL helper function
exec(), we can do the alteration as follows, assuming we want to change all columns of type "foo" to "foo2" using a text cast as intermediate (this is the usual way to handle enums):
SELECT exec('alter table ' || t || ' ' || array_to_string(atts,', ')) FROM (SELECT attrelid::regclass AS t, array_agg('alter ' || quote_ident(attname) || ' type foo2 using ' || quote_ident(attname) || '::text::foo2') AS atts FROM pg_attribute WHERE atttypid = 'foo'::regtype GROUP BY attrelid) s;