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








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
Thu, 8 Nov 2001 20:52:45 +0100
=?iso-8859-1?Q?Evelio_Mart=EDnez?= <evelio.martinez@testanet.com>
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


Fri, 09 Nov 2001 07:33:23 +0100
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: How to optimize a column type change???