Forum OpenACS Q&A: How to drop a column in postgres ?

Posted by Yeung Heunry on
As Title , thx a lot.
Posted by Stephen . on
You can't.  You'll have to recreate the table without the column, drop the original table, rename the new to the old.
Posted by Reuven Lerner on

This is in the PostgreSQL FAQ:

4.4) How do you remove a column from a table?

We do not support ALTER TABLE DROP COLUMN, but do this:

    LOCK TABLE old_table;
    SELECT ...  -- select all columns but the one you want to remove
    INTO TABLE new_table
    FROM old_table;
    DROP TABLE old_table;
    ALTER TABLE new_table RENAME TO old_table;

Yes, this is annoying. According to the to do list, it looks like this will be fixed in the upcoming 7.3 release.

Posted by Dan Wickstrom on
You have to be careful with this approach, as it will drop all of the foreign key constraints and triggers associated with this table.  When I do this, if I know that the table has associated foreign key constraints, I do a before and after dump and compare them to make sure that I added the foreign key constraints back correctly.
Posted by Don Baccus on
On a production system running PG 7.2 or earlier I'd recommend just keeping the column, because if the issue raised by Dan.  A development system's a different matter, though.

PG 7.3 will be in beta next month and as was pointed out should include DROP COLUMN.

Posted by Robert Locke on
I'm not sure about this, but I think creating a new table and renaming it to the old table's name may also break any views which depended on the old table, even if said views don't reference the dropped column.

Is that correct?