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