My problem is that my two outer joined tables have columns that have the 
same names.  Therefore when my select list tries to reference the 
columns they are ambiguously defined.  Looking at the doc I see the way 
to deal with this is by using the following syntax:

table as alias (column1alias, column2alias,...)

So we can alias the conficting column names to resolve the problem. 
However the problem with this is that the column aliases are positional 
per the table structure. Thus column1alias applies to the first column 
in the table. Code that relies on the order of columns in a table is 
very brittle. As adding a column always places it at the end of the 
table, it is very easy to have a newly installed site have one order 
(the order the create table command creates them in) and a site 
upgrading from an older version (where the upgrade simply adds the new 
columns) to have column orders be different.

My feeling is that postgres has misinterpreted the SQL92 spec in this 
regards. But I am having problems finding an online copy of the SQL92 
spec so that I can verify.

What I would expect the syntax to be is:

table as alias (columna as aliasa, columnb as aliasb,...)

This will allow the query to work regardless of what the table column 
order is.  Generally the SQL spec has tried not to tie query behaviour 
to the table column order.

I will fix my code so that it works given how postgres currently 
supports the column aliases.

Can anyone point me to a copy of the SQL92 spec so that I can research 
this more?

thanks,
--Barry


Fri, 16 Mar 2001 10:17:33 -0800
Barry Lind <barry@xythos.com>
Problems with outer joins in 7.1beta5
On Fri, Mar 16, 2001 at 10:17:33AM -0800, Barry Lind wrote:
> 
> My feeling is that postgres has misinterpreted the SQL92 spec in this 
> regards. But I am having problems finding an online copy of the SQL92 
> spec so that I can verify.
> 
> What I would expect the syntax to be is:
> 
> table as alias (columna as aliasa, columnb as aliasb,...)
> 
> This will allow the query to work regardless of what the table column 
> order is.  Generally the SQL spec has tried not to tie query behaviour 
> to the table column order.
> 

What you expect, and what's in the spec. can be very different. As
the following quote shows, the definition is in fact order dependent:
note that a <derived column list> is a simple comma delimited list of
column names.

Quote from SQL'92:

    6.3  <table reference>

    Function

    Reference a table.

    Format

    <table reference> ::=
           <table name> [ [ AS ] <correlation name>
               [ <left paren> <derived column list> <right paren> ] ]
         | <derived table> [ AS ] <correlation name>
               [ <left paren> <derived column list> <right paren> ]
         | <joined table>

    <derived table> ::= <table subquery>

    <derived column list> ::= <column name list>

    <column name list> ::=
         <column name> [ { <comma> <column name> }... ]


    Syntax Rules

[...]

    7) If a <derived column list> is specified in a <table reference>,
       then the number of <column name>s in the <derived column list>
       shall be the same as the degree of the table specified by the
       <derived table> or the <table name> of that <table reference>,
       and the name of the i-th column of that <derived table> or the
       effective name of the i-th column of that <table name> is the
       i-th <column name> in that <derived column list>.


Fri, 16 Mar 2001 13:34:45 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: [HACKERS] Problems with outer joins in 7.1beta5
Barry Lind <barry@xythos.com> writes:
> What I would expect the syntax to be is:
> table as alias (columna as aliasa, columnb as aliasb,...)
> This will allow the query to work regardless of what the table column 
> order is.  Generally the SQL spec has tried not to tie query behaviour 
> to the table column order.

Unfortunately, the spec authors seem to have forgotten that basic design
rule when they wrote the aliasing syntax.  Column alias lists are
position-sensitive:

         <table reference> ::=
                <table name> [ [ AS ] <correlation name>
                    [ <left paren> <derived column list> <right paren> ] ]
              | <derived table> [ AS ] <correlation name>
                    [ <left paren> <derived column list> <right paren> ]
              | <joined table>

         <derived column list> ::= <column name list>

         <column name list> ::=
              <column name> [ { <comma> <column name> }... ]

SQL99 seems to be no better.  Sorry.

			regards, tom lane

Fri, 16 Mar 2001 14:58:38 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Problems with outer joins in 7.1beta5 