Thread: Is there any way to delete a column

On Monday 10 March 2003 10:13, shreedhar wrote:
> To delete a column from table , I am following the method as per postgresl
> documents.
>
> 1) creating emptable with same structure and moving all records.
> 2) creating another table with out column which you have remove.
> 3) Creating all constraints.
> 4) moving all data from temp table to new table and removing temp table.
>
> Is there any way to delete a column, other than above method

Upgrade to 7.3 :-)

Ian Barwick
barwick@gmx.net

In 7.3.x,
ALTER TABLE DROP COLUMN column_name




Ian Barwick <barwick@gmx.net>@postgresql.org on 03/10/2003 04:51:51 AM

Sent by:    pgsql-general-owner@postgresql.org


To:    "shreedhar" <shreedhar@lucidindia.net>, "Postgre General"
       <pgsql-general@postgresql.org>
cc:
Subject:    Re: [GENERAL] Is there any way to delete a column


On Monday 10 March 2003 10:13, shreedhar wrote:
> To delete a column from table , I am following the method as per
postgresl
> documents.
>
> 1) creating emptable with same structure and moving all records.
> 2) creating another table with out column which you have remove.
> 3) Creating all constraints.
> 4) moving all data from temp table to new table and removing temp table.
>
> Is there any way to delete a column, other than above method

Upgrade to 7.3 :-)

Ian Barwick
barwick@gmx.net


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Also I recommend that you explicitly add constraint names for columns
with requirements. That way, you can cleanly drop columns with minimum
fuss.

e.g Normally people just do this

create table foo(
     id       BIGINT DEFAULT( nextval('sequence_foo_gen') ) NOT NULL,
     other_table  BIGINT NOT NULL,
     some_text  TEXT CHECK( some_text <> '' ) NOT NULL,
     FOREIGN KEY( other_table ) REFERENCES other_table( id ) ON UPDATE
CASCADE ON DELETE CASCADE,
     PRIMARY KEY( id )
);

Problem with this is that I've found it difficult to drop the foreign
key or even the checks. So, if you do the following....

create table foo(
     id       BIGINT DEFAULT( nextval('sequence_foo_gen') ) NOT NULL,
     other_table  BIGINT NOT NULL,
     some_text  TEXT NOT NULL,

     CONSTRAINT chk_foo_some_txt CHECK( some_text <> '' ),
     CONSTRAINT fk_foo_other_table FOREIGN KEY( other_table ) REFERENCES
other_table( id ) ON UPDATE CASCADE ON DELETE CASCADE,
     CONSTRAINT pk_foo PRIMARY KEY( id )
);

You now have ultimate control over the tables constraints. Therefore, if
you wanted to drop the column some_text. It's simply a matter of ALTER
TABLE foo DROP CONSTRAINT chk_foo_some_text; THEN ALTER TABLE foo DROP
COLUMN some_text.

Easy.

Hope this helps.

H

On Mon, 2003-03-10 at 22:13, shreedhar wrote:
> To delete a column from table , I am following the method as per
> postgresl documents.
>
> 1) creating emptable with same structure and moving all records.
> 2) creating another table with out column which you have remove.
> 3) Creating all constraints.
> 4) moving all data from temp table to new table and removing temp
> table.
>
> Is there any way to delete a column, other than above method
>
> Regards,
> Sreedhar
>
>
--
Hadley Willan > Systems Development > Deeper Design Limited. +64(7)377-3328
hadley.willan@deeperdesign.co.nz > www.deeperdesign.com > +64(21)-28-41-463
Level 1, 4 Tamamutu St, PO Box 90, TAUPO 2730, New Zealand.