Postgres Pro
Downloads
Home   >   mailing lists

Thread: adding fields

adding fields

From
Jodi Kanter
Date:
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

 

 

 

Re: adding fields

From
Jodi Kanter
Date:
Thank you. I found this doc just after I sent the email. I appreciate the quick response.
Jodi
----- Original Message -----
Sent: Friday, January 18, 2002 10:14 AM
Subject: RE: [ADMIN] adding fields

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

 

 

 

Re: adding fields

From
"Heather Johnson"
Date:
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;
 
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;
 
Then you can do:
 
BEGIN WORK;
UPDATE table_adding_stuff_to SET new_column_name = 'default value';
COMMIT WORK;
 
Heather
----- Original Message -----
Sent: Friday, January 18, 2002 9:55 AM
Subject: [ADMIN] 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

 

 

 

Re: adding fields

From
"Florian Helmberger"
Date:
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 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