Robert Treat <xzilla@users.sourceforge.net> writes:
> On Wed, 2004-05-05 at 10:36, Bruce Momjian wrote:
>> Do we still want this TODO?
>>
>> o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2];
> I think we should leave since it is still functionality that people will
> want.
It's not that no one would want it, it's that the code impact (and risk
of bugs) associated with separate logical and physical column numbers
seems very disproportionate to the value. The main argument for it
AFAIR was to support column type substitution via drop col/add col/
reorder col. Now that we have a better way I think the value of such a
feature wouldn't be worth the work/risk.
> recreating the entire table is likely to cause excessive i/o and disk
> space issues compared to a potentially much nicer add column/update
> column/drop column routine.
How you figure that? The UPDATE step will in itself require 2X disk
space --- and after that you'll need a VACUUM FULL to get it back.
The implementation Rod came up with is much nicer.
> Hmm... upon further thought, if the above implementation stands up, istm
> that its machinations could also be used to implement the reordering
> functionality... ie. rewrite the table and fix up any dependencies as
> needed.
True. In fact, this example that I put into the regression tests may be
food for thought:
create table another (f1 int, f2 text);
insert into another values(1, 'one');
insert into another values(2, 'two');
insert into another values(3, 'three');
select * from another;f1 | f2
----+------- 1 | one 2 | two 3 | three
(3 rows)
alter table another alter f1 type text using f2 || ' more', alter f2 type bigint using f1 * 10;
select * from another; f1 | f2
------------+----one more | 10two more | 20three more | 30
(3 rows)
regards, tom lane
