Re: Add Column BEFORE/AFTER another column

Erik Jones <erik(at)myemma(dot)com> writes:
> ... The usual answer, or argument against, is because the
> standard dictates that the order of attributes in rows returned by
> queries is undefined in the absence a specified (in the query) ordering.

I don't think this is true. The spec is explicit that *row* order is
indeterminate without an ORDER BY clause, eg SQL92 section 20.2 GR4:

4) If an <order by clause> is not specified, then the ordering of
the rows of Q is implementation-dependent.

However, *column* order seems always respected, eg 7.9 rule 3 saith
about SELECT * :

b) Otherwise, the <select list> "*" is equivalent to a <value
expression> sequence in which each <value expression> is a
<column reference> that references a column of T and each
column of T is referenced exactly once. The columns are ref-
erenced in the ascending sequence of their ordinal position
within T.

Another example is that section 11.11 is crystal clear that ALTER TABLE
ADD COLUMN puts the new column at the end:

4) In all other respects, the specification of a <column
definition> in an <alter table statement> has the same effect
as specification of the <column definition> in the <table
definition> for T would have had. In particular, the degree of T
is increased by 1 (one) and the ordinal position of that column
is equal to the new degree of T as specified in the General
Rules of Subclause 11.4, "<column definition>".

AFAIK the convention that "using SELECT * in code is a bad idea" is
folklore born from years of maintaining real applications; it's surely
good advice, but it's not grounded in any vagueness of the spec.

As far as real PG plans go, I've lost track of the number of requests
we've had for the ability to adjust column order. To do this in a
reasonably sane fashion, we have to separate logical from physical
column order; and the reason that's not been tackled is fear of bugs
from using the wrong kind of column number in the wrong place. It'll
probably get done someday, but my advice would be to stay away from
the first major release after it happens ...

regards, tom lane