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
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 |
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 |