Skip site navigation (1) Skip section navigation (2)

Re: delete column

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my>
Cc: "Hillensbeck, Preston" <PHillensbeck(at)sfbcic(dot)com>,"'webmaster'" <webmaster(at)harbornet(dot)com>,"'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org>
Subject: Re: delete column
Date: 2002-04-27 03:03:43
Message-ID: 200204270303.g3R33hZ13788@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Mikail MajorovDate: 2002-04-27 04:25:58
Subject: Close access on DB or TABLE.
Previous:From: postgresDate: 2002-04-27 02:52:35
Subject: intel vs amd benchmark for pg server part 2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group