On Fri, 2 May 2003, A.Bhuvaneswaran wrote:
> > Hi,
> >
> > I would like to change column type from int8 to int4 in psql command
> > line utility. I am run command ALTER TABLE blah blah blah, but nothing
> > happens. No error message and no change of column type. Anyone have a
> > clue what is happening?
>
> Way 1:
>
> => BEGIN;
> => ALTER TABLE my_table ADD COLUMN new_column int4;
> => UPDATE my_table SET new_column = old_column;
> => ALTER TABLE my_table DROP COLUMN old_column;
> => COMMIT;
>
> Way 2:
>
> =# BEGIN;
> =# UPDATE pg_attribute SET atttypid = pg_type.oid WHERE attrelid =
> pg_class.oid AND pg_class.relnamespace = pg_namespace.oid AND
> pg_namespace.nspname = 'my_schema' AND pg_class.relname = 'my_table' AND
> pg_attribute.attname = 'my_column' AND pg_type.typname = 'int4';
> =# COMMIT;
I think this second way is a bad idea if there's already data in
the table since I don't think it'll preserve values properly and
in simple tests has gotten me crashes depending on what follows it.