Mass type replacement

Administrative Snippets

Mass type replacement

Works with PostgreSQL

>=8.4

Written in

SQL

Depends on

Nothing

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;