Re: delete column

Lincoln Yeoh wrote:
> 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 :).
>

Good. However, why do you do the 'create new table' when the SELECT
INTO creates the table?

FAQ updated to take advantage of rollback-able DROP TABLE:

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;

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026