Changing column type from oid to int4

Mario Weilguni <mweilguni@sime.com> writes:

Did somebody ever try to change the type to a compatible type? I plan to
change a column from oid to int (once it holded a lob, now some sort of id)
with modifying pg_attribute:

update pg_attribute
set atttypid=(select oid from pg_type where typname='int4')
where attname='whatever'
and attrelid=(select oid from pg_class where relname='a_table_name');

It seems to work fine, but I was not sure so I rolled back the change.

You'd have to look at indexes and constraints involving the column, too.

Another point is the pg_depend linkage between the column and the
datatype. We short-circuit this for built-in types, so it's not really
an issue when switching from one built-in datatype to another, but you'd
have to fix it if you were say changing to a domain type.

regards, tom lane