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@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026