Re: ALTER TABLE modifications

On Fri, 2003-11-14 at 09:57, Peter Eisentraut wrote:
> Rod Taylor writes:
>
> > The method is rename old column, add new column, move data across, move
> > or reform dependencies, drop old column.
>
> I can do this by hand. If we have an explicit command to do it, then it
> needs to preserve the table schema. Else, this feature would be mostly
> useless and a certain source of complaints.

The method was agreed to on -hackers prior to any code having been
written. Unless I'm mistaken, the method was suggested to me by other on
-hackers.

Can you give me an alternative on how to approach this problem without
relying on another TODO item labelled ALTER TABLE .. POSITION?

The point of the command isn't to accomplish anything magical, simply to
make it easier.

begin;
Rename old column
Add new column
Copy data
Recreate default
Recreate NOT NULL constraint
Recreate index 1
Recreate index 2 (multi-key)
Recreate check constraint 1
Recreate check constraint 2
Recreate check constraint 3
Recreate foreign key 1
remove old column
commit;
vacuum full table (to get rid of 2x problem since the user was unable to
do a fileswap)

The above is by hand, the below is with this command:

begin;
Alter table .. type ..
commit;

If it becomes possible to change the order without having to goto an
exceptional amount of work (that POSITION item) then it would only be
obvious to attempt to preserve the position.