Thread: adding fields to a table
Hello, I have a table with a bunch of records already inserted. When I want to add or remove fields from the, what I've done isproduce an sql file, add the fields to the create table directive, and add the fields manually to each record to be inserted.Then I do an \i db_name which destroys all the tables and records and then recreates them. I'm wondering if there's a better way. Namely, I alterthe table using psql, which I know can be done. What I'm not sure about is whether altering the table will also alterthe records, or will the records inserted previous to the alter table be out of sync with the table. Thanks for your advice. Mark
> I have a table with a bunch of records already inserted. When > I want to add or remove fields from the, what I've done is > produce an sql file, add the fields to the create table > directive, and add the fields manually to each record to be > inserted. Then I do an > > \i db_name > > which destroys all the tables and records and then recreates > them. I'm wondering if there's a better way. Namely, I alter > the table using psql, which I know can be done. What I'm not > sure about is whether altering the table will also alter the > records, or will the records inserted previous to the alter > table be out of sync with the table. You can use ALTER TABLE. ALTER TABLE <table> ADD [column] column type Can also rename columns, add constraints, change to NOT NULL, etc. However you cannot currently remove a column... ALTER TABLE in SQL COMMANDS at: http://www.postgresql.com/docs/pdf/7.3/reference-7.3.2-A4.pdf
On Thursday 26 June 2003 18:34, Benjamin Jury wrote: (...) > > You can use ALTER TABLE. > > ALTER TABLE <table> ADD [column] column type > > Can also rename columns, add constraints, change to NOT NULL, etc. > > However you cannot currently remove a column... Oh yes you can: ALTER TABLE tbl DROP COLUMN whatever http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=ddl-alter.html IIRC it was introduced fairly recently (7.3?) Ian Barwick barwick@gmx.net
ALTER TABLE ADD COLUMN On Thu, Jun 26, 2003 at 12:42:18PM -0400, MT wrote: > Hello, > > I have a table with a bunch of records already inserted. When I want to add or remove fields from the, what I've done isproduce an sql file, add the fields to the create table directive, and add the fields manually to each record to be inserted.Then I do an > > \i db_name > > which destroys all the tables and records and then recreates them. I'm wondering if there's a better way. Namely, I alterthe table using psql, which I know can be done. What I'm not sure about is whether altering the table will also alterthe records, or will the records inserted previous to the alter table be out of sync with the table. > > Thanks for your advice. > > Mark > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "the West won the world not by the superiority of its ideas or values or > religion but rather by its superiority in applying organized violence. > Westerners often forget this fact, non-Westerners never do." > - Samuel P. Huntington
Attachment
> > However you cannot currently remove a column... > > Oh yes you can: > > ALTER TABLE tbl DROP COLUMN whatever > > http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&fil > e=ddl-alter.html > > IIRC it was introduced fairly recently (7.3?) Doh! Really should get rid of 'Practical PostgreSQL' as my point of reference, only covers 7.1.x... Come on O'Reilly, print a second edition!
On Fri, Jun 27, 2003 at 09:53:38 +0100, Benjamin Jury <benjamin.jury@mpuk.com> wrote: > > Doh! Really should get rid of 'Practical PostgreSQL' as my point of > reference, only covers 7.1.x... Come on O'Reilly, print a second edition! I find the online docs are pretty useful for checking for this kind of thing. My main problem is I am getting used to what is available in CVS and don't always remember what isn't in earlier releases.