Re: Altering a table - positioning new columns

Manfred Koizar <mkoi-pg@aon.at> writes:
> On the other hand, ALTER TABLE t ADD COLUMN c3 int AFTER c1;
> would require Postgres to convert existing tuples:

It could actually be done without that.  If we distinguished logical and
physical column numbers (this'd require a new attlognum column in
pg_attribute), then we could reorganize the apparent column ordering
at will, without touching the contents of any existing tuple.  ADD
COLUMN would still assign N+1 as the physical column number of a new
column, but the logical numbering could be relabeled to insert the
new column anywhere.

We discussed this last summer, IIRC, and decided that we didn't want to
get into it because of the probability of introducing ugly new bugs
as a result of using logical attnum in places where physical attnum
is needed, or vice versa.  Since in simple test cases the numbers would
be the same, such bugs could escape detection for a long time (just long
enough to fatally trash someone's data :-().  But maybe some day we'll
tackle it.

            regards, tom lane