Re: alter table add column - specify where the column will go?

Stuart McGraw <smcg2297(at)frii(dot)com> writes:
> This is the first time I've ever looked at the 1000+ page spec and I
> haven't tried to chase down all the definitions so I don't pretend to
> be authoritative but it sure sounds to me (as your observation above
> implies) that SQL *does* have an explicit notion of column order.

Yes, it does. If it did not, they would never have provided the option
of omitting the target-column-name list from INSERT.

As for the original issue, the ability to add a column somewhere other
than at the end is on the TODO list, but it's been there for quite some
time so don't hold your breath waiting for it to get done. There are
several discussions in the pgsql-hackers archives about why it isn't
a simple thing to do.

In the meantime, if the OP wants it bad enough he can do something
involving CREATE TABLE ... AS SELECT ... to build a new table with
the columns in the desired order, and then rename it to replace the
old table.

regards, tom lane