Обсуждение: adding fields
I need to add a field to an existing table that contains a good amount of data. Do I need to drop table, recreate, and then reload data to do this? I cannot seem to find any documentation to say otherwise.
Thanks
Jodi
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu
Thank you. I found this doc just after I sent the email. I appreciate the quick response.
Jodi
----- Original Message -----From: Florian HelmbergerTo: Jodi KanterSent: Friday, January 18, 2002 10:14 AMSubject: RE: [ADMIN] adding fieldsHi.You should useALTER TABLE <table> ADD COLUMN <column> <datatyp>;instead. Also, take a look at the docs:Cheers,Florian-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Jodi Kanter
Sent: Friday, January 18, 2002 3:56 PM
To: Postgres Admin List
Subject: [ADMIN] adding fieldsI need to add a field to an existing table that contains a good amount of data. Do I need to drop table, recreate, and then reload data to do this? I cannot seem to find any documentation to say otherwise.ThanksJodi_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu
Hi Jodi--
No, you shouldn't have to go to that amount of trouble. Try this instead (change the name of the table, the default value, the column name, and the column type to suit your needs):
BEGIN WORK;
LOCK TABLE table_adding_stuff_to IN ACCESS EXCLUSIVE MODE;
LOCK TABLE table_adding_stuff_to IN ACCESS EXCLUSIVE MODE;
ALTER TABLE table_adding_stuff_to ADD COLUMN new_column_name COLUMNTYPE;
ALTER TABLE table_adding_stuff_to ALTER COLUMN new_column_name SET DEFAULT 'default value';
COMMIT WORK;
ALTER TABLE table_adding_stuff_to ALTER COLUMN new_column_name SET DEFAULT 'default value';
COMMIT WORK;
Then you can do:
BEGIN WORK;
UPDATE table_adding_stuff_to SET new_column_name = 'default value';
COMMIT WORK;
Heather
----- Original Message -----From: Jodi KanterSent: Friday, January 18, 2002 9:55 AMSubject: [ADMIN] adding fieldsI need to add a field to an existing table that contains a good amount of data. Do I need to drop table, recreate, and then reload data to do this? I cannot seem to find any documentation to say otherwise.ThanksJodi_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu
Hi.
You should use
ALTER TABLE <table> ADD COLUMN <column> <datatyp>;
instead. Also, take a look at the docs:
Cheers,
Florian
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Jodi Kanter
Sent: Friday, January 18, 2002 3:56 PM
To: Postgres Admin List
Subject: [ADMIN] adding fieldsI need to add a field to an existing table that contains a good amount of data. Do I need to drop table, recreate, and then reload data to do this? I cannot seem to find any documentation to say otherwise.ThanksJodi_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu