Thread: delete column

delete column

From
webmaster
Date:
I know this is an easy question, but I can't find any info on how do
this in the PostgreSQL book.  How do you delete a column?  I've been
able to delete tables, db's, rows, etc.  But can't figure out how to
just delete a column.  I'm running RedHat 7.2 and PostgreSQL 7.1

Thanks for any help.


Re: delete column

From
"Hillensbeck, Preston"
Date:
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 :)


-----Original Message-----
From: webmaster [mailto:webmaster@harbornet.com]
Sent: Friday, April 26, 2002 7:51 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] delete column


I know this is an easy question, but I can't find any info on how do
this in the PostgreSQL book.  How do you delete a column?  I've been
able to delete tables, db's, rows, etc.  But can't figure out how to
just delete a column.  I'm running RedHat 7.2 and PostgreSQL 7.1

Thanks for any help.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: delete column

From
wsheldah@lexmark.com
Date:


1. Back up your database.
2. Use pg_dump to save the schema and data for that table to a text file
3. Edit the file to add a DROP TABLE statement before the CREATE TABLE
statement,
and edit the CREATE TABLE statement to omit the column you want to drop.
4. Make sure your data is in the file and is intact.
5. Use psql to execute the commands in the file you just edited.

You'll also want to take steps to be sure no one attempts to update the table
between the time you dump it and the time it finishes restoring.

You may also want to take a look at the TODO list on the postgresql website and
estimate how long it may be before support for DROP COLUMN is added, and support
for ALTER COLUMN improves. Personally, I haven't had a problem with this
procedure, but my dataset is relatively small and I've incurred very little
downtime the few times I've had to do this.

Wes Sheldahl




webmaster <webmaster%harbornet.com@interlock.lexmark.com> on 04/26/2002 08:50:53
AM

To:   pgsql-general%postgresql.org@interlock.lexmark.com
cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  [GENERAL] delete column


I know this is an easy question, but I can't find any info on how do
this in the PostgreSQL book.  How do you delete a column?  I've been
able to delete tables, db's, rows, etc.  But can't figure out how to
just delete a column.  I'm running RedHat 7.2 and PostgreSQL 7.1

Thanks for any help.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org





Re: delete column

From
Bruce Momjian
Date:
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.

--
  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

Re: delete column

From
"Ian Harding"
Date:
But you lose all your RI constraints.  I keep the RI constraints in a separate file so I can re-create them when I need
to... 

>>> "Hillensbeck, Preston" <PHillensbeck@sfbcic.com> 04/26/02 01:49PM >>>
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 :)


-----Original Message-----
From: webmaster [mailto:webmaster@harbornet.com]
Sent: Friday, April 26, 2002 7:51 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] delete column


I know this is an easy question, but I can't find any info on how do
this in the PostgreSQL book.  How do you delete a column?  I've been
able to delete tables, db's, rows, etc.  But can't figure out how to
just delete a column.  I'm running RedHat 7.2 and PostgreSQL 7.1

Thanks for any help.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly


Re: delete column

From
Lincoln Yeoh
Date:
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.



Re: delete column

From
Bruce Momjian
Date:
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

Re: delete column

From
Lincoln Yeoh
Date:
Oops my mistake!  I actually didn't use select into to create the table,
because I didn't want the columns exactly the same as before - was
switching from varchar to text.

:).

Link.

At 11:03 PM 4/26/02 -0400, Bruce Momjian wrote:

>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:



Re: delete column

From
Bruce Momjian
Date:
Lincoln Yeoh wrote:
> >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:
>
> Oops my mistake!  I actually didn't use select into to create the table,
> because I didn't want the columns exactly the same as before - was
> switching from varchar to text.

Oh, yes, that makes sense for your case. You can't control the data
types with SELECT INTO.

--
  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

Re: delete column

From
Mathieu Arnold
Date:

--On vendredi 26 avril 2002 15:49 -0500 "Hillensbeck, Preston"
<PHillensbeck@sfbcic.com> 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 not really good, as you loose all defaults from the table.
I believe that
CREATE TABLE new_table (fields without the one you want);
INSERT INTO new_table SELECT ... FROM old_table;
DROP TABLE old_table;
ALTER TABLE new_table RENAME to old_table;


--
Mathieu Arnold

Re: delete column

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Oops my mistake!  I actually didn't use select into to create the table,
>> because I didn't want the columns exactly the same as before - was
>> switching from varchar to text.

> Oh, yes, that makes sense for your case. You can't control the data
> types with SELECT INTO.

You could with an explicit cast:

    SELECT varcharcol::text INTO newtable FROM ...

            regards, tom lane

Re: delete column

From
Jochem van Dieten
Date:
[retry with correct address]

Bruce Momjian wrote:
 >
 > 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;

Depending on the dataset and the dominant query types I frequently add
an ORDER BY clause to force a sort on the data as it resides on the
disk. Not very usefull for datasets that change a lot, but it can help
for some queries.

      BEGIN;
      LOCK TABLE old_table;
      SELECT ...  -- select all columns but the one you want to remove
      INTO TABLE new_table
      FROM old_table
      ORDER BY dominant_column;
      DROP TABLE old_table;
      ALTER TABLE new_table RENAME TO old_table;
      COMMIT;

Jochem


Re: delete column

From
Mathieu Arnold
Date:

--On samedi 27 avril 2002 11:08 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>>> Oops my mistake!  I actually didn't use select into to create the
>>> table,  because I didn't want the columns exactly the same as before -
>>> was  switching from varchar to text.
>
>> Oh, yes, that makes sense for your case. You can't control the data
>> types with SELECT INTO.
>
> You could with an explicit cast:
>
>     SELECT varcharcol::text INTO newtable FROM ...

I really think that :
CREATE new (all without the one(s) we don't want and their defaults)
INSERT INTO new select ... from old;
DROP old;
ALTER TABLE new RENAME TO old;

is the better.

--
Mathieu Arnold

Re: delete column

From
Michael Meskes
Date:
On Fri, Apr 26, 2002 at 03:49:49PM -0500, Hillensbeck, Preston wrote:
> There isn't a DROP COLUMN function yet, but you can do this...
> ...

I know that we have support for DROP COLUMN in our TODO list, but I'd
like to know if anyone is working on this.

Michael
--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!