Thread: Changing colums type

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?

Thanks in advance for any suggestion(s).


*********************************************
*   Best Regards   ---   Andrei Verovski
*
*   Personal Home Page
*   http://snow.prohosting.com/guru4mac
*   Mac, Linux, DTP, Development, IT WEB Site
*********************************************

> 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;

Hope it helps.

regards,
bhuvaneswaran

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.