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