Thread: drop column?

This is something I have been wanting for a long time.  The ability to
update or drop a column from a table.

Right now you have to select the columns you want into a temp table and
drop the working table and rename the temp table to the working table.

|On 020123 12:15
|Jodi Kanter (jkanter@virginia.edu) wrote the following...
|
|I cannot seem to locate any documentation about removing a column that I just added to a table. There is no data in
thatparticular column but there is in the rest of the table. 
|Can I remove this column or will I need to drop the entire table and restore from backup?
|Thanks
|Jodi
|_______________________________
|Jodi L Kanter
|BioInformatics Database Administrator
|University of Virginia
|(434) 924-2846
|jkanter@virginia.edu
--
BOFH Reason of the day: not properly grounded, please bury computer
                                               _   |       _
                                              (_()(|('.|)('||.|()|`|(

>Can I remove this column or will I need to drop the entire table and
restore from backup?

There's not a "remove column" option for alter table, but you may be able to
get around restoring by renaming the existing table, creating a replacement
the way you want it to look, & then copy from the old to new using "insert
into table newtable (col1,col2) values (select col1,col2 from oldtable);"

-Nick

--------------------------------------------------------------------------
Nick Fankhauser  nickf@ontko.com  Phone 1.765.935.4283  Fax 1.765.962.9788
Ray Ontko & Co.     Software Consulting Services     http://www.ontko.com/

There are some ways you can do for this prupose.

Create a new table in that database without that columns. you can do this very easily:

1. pg_dump -t table_name -s your_dbname > anyname.dump
   the switch -s means without data, schem only. it is a very simple ascii file and readable

2. using some edit tool to open file anyname.dump and remove the columns you don't want and change the table name as a new one like "table_new" and do:

3. cat anyname.dump | psql your_dbname

4. psql your_db_name
psql> insert into table_new select field1, field2, ... from ori_table;
psql> drop ori_table;
psql> alter table rename table_new ori_table;

Then it is done.

PS. for safe, dump your entire database first

Banghe

Jodi Kanter wrote:

I cannot seem to locate any documentation about removing a column that I just added to a table. There is no data in that particular column but there is in the rest of the table.Can I remove this column or will I need to drop the entire table and restore from backup?ThanksJodi

_______________________________

Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Jodi,

Of course, we should have 'alter table .... drop column ....' statement.
>Can I remove this column or will I need to drop the entire table and
restore from backup?
You can do as following:
select fields_you_want_to_keep into new_table from old_table.
Make sure that this statement execute successfully. Then drop the old table
and change table name (alter table ... rename to ...).
I am a newbie of postgres.

lonh
----- Original Message -----
From: Jodi Kanter
To: Postgres Admin List
Sent: Thursday, January 24, 2002 2:54 AM
Subject: [ADMIN] drop column?


I cannot seem to locate any documentation about removing a column that I
just added to a table. There is no data in that particular column but there
is in the rest of the table.
Can I remove this column or will I need to drop the entire table and restore
from backup?
Thanks
Jodi
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu