Re: Simple Column reordering

"Simon Riggs" <simon@2ndquadrant.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
<valueexpression> is a             <column reference> that references a column of T and each             column of T is
referencedexactly 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,
"<columndefinition>" specify           the degree of the table being created during the definition of           columns
inthat 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
positionincluded           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