delete column

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

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

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

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

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.

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

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:

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

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

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

[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

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

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!