How to optimize a column type change???

Started by Evelio Martínezabout 24 years ago2 messages
#1Evelio Martínez
evelio.martinez@testanet.com

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

#2Jean-Michel POURE
jm.poure@freesurf.fr
In reply to: Evelio Martínez (#1)
Re: 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:

Show quoted text

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