Re: Bug: aliasing in ORDER BY when UNIONing

Marko Kreen <marko(at)l-t(dot)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