At 04:58 PM 4/26/02 -0400, Bruce Momjian wrote:
Hillensbeck, Preston wrote:
> There isn't a DROP COLUMN function yet, but you can do this...
>
> 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;
>
> This is straight out of Bruce Momjian's book, so you can give him
credit for
> this :)
This is from the FAQ, which appears in my book. I think I wrote that
too, or at least with help from others. Wish we had a cleaner way, but
right now, that is all we have.
The following variant makes use of Postgresql's advantages:
BEGIN;
create new_table ... -- the way you want it to be
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;
I did something similar on a production server (after backing up just in
case and testing on a test db) and it worked well. So 3 cheers for
rollback/commits of drop table :).
Got to be careful to get any sequences right tho (grrr!).
Link.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
| 
