Thread: Re: [GENERAL] Is it possible to drop a column?

Well this should also work as well, and it will reduce the heartache.

  say you have a table foo, with columns A,B,C already created. Then
you want to remove column C as you don't need the data anymore.

 So you can do this:

    select A,B into table bar from foo, and it will create a brand new
table called bar , with the attribs for A & B the same as in foo, of
course with all your data included.

-lorenzo


---Jose' Soares <jose@sferacarta.com> wrote:
>
> The SQL command is:
>
>    ALTER TABLE table DROP [COLUMN] column { RESTRICT | CASCADE }
>
> but PostgreSQL hasn't this feature yet.
>
> Currently, to remove an existing column the table must be recreated
> and reloaded. For example, if want to remove field "address" from
table
> "distributors"
> you have to...
>
>
> distributors:
> ----------------------
> field    type
> ----------------------
> did    DECIMAL(3)
> name    VARCHAR(40)
> address    VARCHAR(40)
> ----------------------
>
>
>           CREATE TABLE temp AS SELECT did, city FROM distributors;
>           DROP TABLE distributors;
>           CREATE TABLE distributors (
>                did      DECIMAL(3)  DEFAULT 1,
>                name     VARCHAR(40) NOT NULL,
>                );
>
>           INSERT INTO distributors SELECT * FROM temp;
>           DROP TABLE temp;
>
>
> sim wrote:
> >
> > Hello,
> >
> > Is it possible to drop a column?
> >
> > Thanks.
>
>
> Jose'
>
>

_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com

At 21:25 +0200 on 13/9/98, Lorenzo Huerta wrote:


> Well this should also work as well, and it will reduce the heartache.
>
>   say you have a table foo, with columns A,B,C already created. Then
> you want to remove column C as you don't need the data anymore.
>
>  So you can do this:
>
>     select A,B into table bar from foo, and it will create a brand new
> table called bar , with the attribs for A & B the same as in foo, of
> course with all your data included.

One must remember, however, that this table does not retain any defaults or
constraints from the original table, nor does it have any indices.

While it is easy to define indices after the table is already created and
filled, default and constraints are another story altogether.

So, if your original table has them, the method that defines the new table
first, and copies the data later, is the preferred method.

I find that SELECT INTO TABLE is good mostly for the creation of temporary
tables.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma