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

Collapse
1: How to drop a column in postgres ?
Posted by Yeung Heunry on 08/06/02 11:04 AM
As Title , thx a lot.
Collapse
2: Response to How to drop a column in postgres ? (response to 1)
Posted by Stephen . on 08/06/02 11:09 AM
You can't.  You'll have to recreate the table without the column, drop the original table, rename the new to the old.
Collapse
3: Response to How to drop a column in postgres ? (response to 1)
Posted by Reuven Lerner on 08/06/02 11:46 AM

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:

    BEGIN;
    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;
    COMMIT;

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

Collapse
4: Response to How to drop a column in postgres ? (response to 1)
Posted by Dan Wickstrom on 08/06/02 01:29 PM
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.
Collapse
5: Response to How to drop a column in postgres ? (response to 1)
Posted by Don Baccus on 08/06/02 03:15 PM
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.

Collapse
6: Response to How to drop a column in postgres ? (response to 1)
Posted by Robert Locke on 08/09/02 03:33 PM
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?