Thread: Add/Remove Columns

Add/Remove Columns

From
"Michael Ansley"
Date:
>>>   Jackson, DeJuan wrote:
>>>   > > Martin Schulze wrote:
>>>   > >   > . I wonder how one could add or remove columns from existing tables.
>>>   > >   >
>>>   > >   >   With mSQL this was possible with a trick.  You had to dump the
>>>   > >   >   whole table but you could tell the dump program to add dummy fields
>>>   > >   >   or to leave out existing fields.  Is there any such possibility
>>>   > >   >   with PostgreSQL?
>>>
>>>   > You can do a select into a temp table with the added columns, drop the
>>>   > old table the rename the temp table to the old name.  You'll also have
>>>   > to recreate your indexes, triggers, and rules.
>>>
>>>   I understand.  Looks like PostgreSQL is more preconceived than
>>>   other databases.  I figure out how to do this, though.  I you would
>>>   have an example laying around I'd be very happy receiving it.

Why don't you just use ALTER TABLE, eg:
ALTER TABLE ADD COLUMN colname check (colname <= 1) and (colname >= 0)

or something similar.  If the column is a NOT NULL column, then you must have a DEFAULT option, otherwise, add it
initiallywithout the NULL option, populate it, and then ALTER TABLE ALTER COLUMN including the null option.  Of course
forremoving columns you ALTER TABLE DELETE COLUMN, or possibly REMOVE COLUMN, I can't remember. 

MikeA