Thread: Changing column type from oid to int4

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.

So, has anyone ever tried this?

Regards,
 Mario Weilguni

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