Re: Bug: aliasing in ORDER BY when UNIONing

Marko Kreen <marko@l-t.ee> writes:
> # select o.id from op o union all SELECT -1 order by o.id;
> ERROR:  Relation 'o' does not exist
>> 
>> This is not valid SQL.  For one thing, the table alias "o" is not
>> visible outside the first component SELECT.
>> 
>> Yes, I know 7.0 took it... but its handling of ORDER BY on UNION
>> was pretty darn broken.

> Doh.  But if I have several tables with a field 'id'?  Then only
> way is to use the column number?

You could assign column names:

SELECT o.id as id1, p.id as id2, ... UNION ... ORDER BY id1, id2;

> Anyway such stuff should be documented I guess.  From current
> docs I read that it should work.

Where?

> I would have expected that one
> of the select's aliases would be transferred to ORDER BY but its
> not possible?

The first subselect's column names are transferred to ORDER BY.
        regards, tom lane