Re: alter table add column - specify where the column will go?

On 11/24/2010 03:32 AM, Peter Bex wrote:
> On Wed, Nov 24, 2010 at 09:37:02AM +0000, Grzegorz Jaśkiewicz wrote:
>> just never use SELECT *, but always call columns by names. You'll
>> avoid having to depend on the order of columns, which is never
>> guaranteed, even if the table on disk is one order, the return columns
>> could be in some other.
>
> People have been saying that on this list forever, and I agree you
> shouldn't *depend* on column order, but why does INSERT syntax allow
> you to omit the column names?
>
> INSERT INTO sometable VALUES (1, 2, 3);
>
> If columns inherently don't have an ordering, this shouldn't be
> possible because it would make no sense.

Looking in an old copy of a draft 2003 sql standard,

sec-7.12 (p 341)
which describes queries, Syntax Rules, para 3 describes
the * select list and
3b says,

... The columns are referenced in the ascending sequence of their
ordinal position within T.
...

This is the first time I've ever looked at the 1000+ page spec and I
haven't tried to chase down all the definitions so I don't pretend to
be authoritative but it sure sounds to me (as your observation above
implies) that SQL *does* have an explicit notion of column order.

Perhaps those claiming that no order is guaranteed by SELECT * could
provide some support for that from the SQL standards?