>>> On Wed, Dec 20, 2006 at  5:33 AM, in message
Russell Smith <[EMAIL PROTECTED]> 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
> 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
> is changing.  What is stopping us from switching the column order at
> 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
> wanting to add a column in the middle of the table, but FK
> stop me dropping the table to do the reorder.  If ALTER TABLE would
> 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
> 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
> this option.
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".

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not

Reply via email to