How to optimize a column type change???

From: Evelio Martínez <evelio(dot)martinez(at)testanet(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: How to optimize a column type change???
Date: 2001-11-08 19:52:45
Message-ID: 016601c1688e$efb2c600$4ecd72c3@testanet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Orion 2001-11-08 19:58:49 What's the fastest way to do this?
Previous Message Richard Teviotdale 2001-11-08 19:41:39 Re: searching multiple tables and databases

Browse pgsql-hackers by date

  From Date Subject
Next Message Jim Buttafuoco 2001-11-08 22:08:00 Re: Storage Location Patch Proposal for V7.3
Previous Message Ned Wolpert 2001-11-08 19:50:21 Re: MD5-based passwords