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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
