>>> On Wed, Dec 20, 2006 at 5:33 AM, in message
<45891FA1.5000902@pws.com.au>,
Russell Smith <mr-russ@pws.com.au> wrote:
>
> The 8.1 documentation for ALTER TABLE states the following.
>
> Adding a column with a non-null default or changing the type of an
> existing column will require the entire table to be rewritten. This
may
> take a significant amount of time for a large table; and it will
> temporarily require double the disk space.
>
>
> Now, we are rewriting the table from scratch anyway, the on disk
format
> is changing. What is stopping us from switching the column order at
the
> same time. The only thing I can think is that the catalogs will need
> more work to update them. It's a middle sized price to pay for being
> able to reorder the columns in the table. One of the problems I have
is
> wanting to add a column in the middle of the table, but FK
constraints
> stop me dropping the table to do the reorder. If ALTER TABLE would
let
> me stick it in the middle and rewrite the table on disk, I wouldn't
> care. It's likely that I would be rewriting the table anyway. And
by
> specifying AT POSITION, or BEFORE/AFTER you know for big tables it's
> going to take a while.
>
> Not that I'm able to code this at all, but I'm interested in feedback
on
> this option.
+1
Currently, I often have to make the choice between adding a column at
the "logical" place in relation to the other columns or adding it at the
end. The former requires creating a whole new table, populating it with
INSERT/SELECT, dropping the old table, renaming the new table, and
restoring permissions, constraints, indexes, etc. The latter is a
simple ALTER TABLE. When I choose the former, I save significant time
and reduce errors by using pg_dump to generate a lot of the code; but it
should would be a nice feature if ALTER TABLE could do all this "under
the covers".
-Kevin