Re: Altering a table - positioning new columns

Christoph Dalitz <christoph(dot)dalitz(at)hs-niederrhein(dot)de> writes:
> AFAIK there is no concept like "column order of a relation" in the
> relational model; thus the above MySQL option should not be supported by
> a relational database. What does the SQL standard say about this?

You would think so, but SQL92 does say

b) ... 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.

(I didn't bother to look it up, but I would imagine there's something
similar defining the behavior of INSERT without a column name list,
which is the only other place that column order matters in SQL, AFAIR.)

ALTER ADD COLUMN says

4) In all other respects, the specification of a <column defi-
nition> in an <alter table statement> has the same effect as
specification of the <column definition> in the <table defi-
nition> for T would have had. In particular, the degree of T
is increased by 1 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>".

and ALTER DROP COLUMN says

6) The degree of T is reduced by 1. The ordinal position of all
columns having an ordinal position greater than the ordinal
position of C is reduced by 1.

So the Postgres behavior is per spec. Whether MySQL's extension is
worth the (nontrivial) trouble it'd be to implement is in the eye of
the beholder.

regards, tom lane