Re: Simple Column reordering

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> If this is standards-breaking as you say, I would withdraw immediately.
> I checked the SQL standard and could not see how this would do so. The
> standard states SELECT * would return columns in order; it doesn't say
> what that order should be, nor does CREATE TABLE enforce the ordering to
> be the same as it has specified, AFAICS.

SQL92 7.9 <query specification> defines the meaning of SELECT * as

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.

11.3 <table definition> says

2) The degree of the table being created is initially set to 0; the
General Rules of Subclause 11.4, "<column definition>" specify
the degree of the table being created during the definition of
columns in that table.

and 11.4 <column definition> says

4) The degree of the table T being defined in the containing <table
definition> or <temporary table declaration> or altered by the
containing <alter table statement> is increased by 1.

5) A column descriptor is created that describes the column being
defined. .... The ordinal position included
in the column descriptor is equal to the degree of T.

Now, I will grant you that it doesn't actually say anywhere that the
column definitions in CREATE TABLE must be processed left to right, but
if they meant this behavior to be implementation-dependent they would
have said so. Given the number of places in the spec in which semantics
are directly dependent on ordinal position, I cannot think that that is
intended --- for example, the behavior of <row comparison> becomes
completely undefined if column ordinal positions aren't fixed.

regards, tom lane