Thread: How to optimize a column type change???

The simpler solution is to learn C and add this feature to PostgreSQL
internals.

At 20:52 08/11/01 +0100, you wrote:
>Hello all!
>
>As postgresql does not have alter table modify column or alter table drop
>column, is there
>any simpler way to change a column definition??
>
>For example to change a column varchar(40) to varchar(40)[] here you have
>the steps I follow:
>
>Suppose this table:
>    CREATE TABLE "proy_foto" (
>            "numero" int4 DEFAULT nextval('proy_foto_numero_seq'::text)
> NOT NULL,
>            "idproy" int4,
>            "foto" oid,
>            "nombre" varchar(40),
>            "descrip" text,
>            PRIMARY KEY ("numero")
>    );
>1. Add the new column def
>    alter table proy_foto add nombre2 varchar(40)[];
>    alter table proy_foto add descrip2 text[];
>
>2. Initialize with a default value.
>
>    update proy_foto set nombre2 = '{ "1" }', descrip2 = '{"2"}';
>
>3.Update the columns with their corresponding values.
>
>    UPDATE proy_foto
>      SET nombre2[1] = nombre,
>             descrip2[1] = descrip
>      FROM proy_foto
>      WHERE numero = numero;
>
>4. Initialize the obsolete columns
>
>     update proy_foto set nombre = '', descrip = '';
>
>5. Rename the obsolete columns
>    alter table proy_foto rename column nombre to obsolete1;
>    alter table proy_foto rename column descrip to obsolete2;
>
>6. Rename the new columns with the old name.
>    alter table proy_foto rename column nombre2 to nombre;
>    alter table proy_foto rename column descrip2 to descrip;
>
>
>Any simpler idea?
>
>Thanks in advance
>
>------------
>Evelio Martínez