"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