Thread: 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.
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: >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:
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!