Re: ALTERING A TABLE

Steve Wampler wrote:
>
> Ron Peterson wrote:
> >
> >
> > You can't use ALTER TABLE to change a field's data description.  You'll
> > have to make a new table.  Then use SELECT INTO to move your data.  Then
> > DROP TABLE oldtable.  Then ALTER TABLE tablename RENAME TO newname.
>
> Would this really work?  According to the docs, SELECT INTO creates a
> new table (which must not yet exist).  So this new table
> would have the same field data descriptions as the original, right?
>
> Is the documentation wrong?

No, the doc is at least right that a new table is created.  Not sure what
it does if the table already exists.

I do this task by the following sequence (psuedo-sql here):

    select into temp_mytable * from mytable;
    drop mytable;
    create table mytable (...new defn...);
    insert into mytable (...)
        select ... from temp_mytable

And that works pretty well.  Don't forget you'll have to drop/reload all
dependent functions/triggers.  And if you're using a SERIAL column, don't
mistakenly nuke your sequence object (mytable_id_seq) if you're using one
as a primary key generator, otherwise you'll reset the sequence and get
massive confusion.

Regards,
Ed Loehr