Re: order by alias - doesn't work sometimes? - Mailing list pgsql-bugs

hubert depesz lubaczewski <depesz@depesz.com> writes:
> But:
> =$ select unnest( ARRAY['d', 'c', 'a'] ) x order by x <> 'a';
> ERROR:  column "x" does not exist
> LINE 1: select unnest( ARRAY['d', 'c', 'a'] ) x order by x <> 'a';
>                                                          ^

> Obviously I can put the unnest() call in from, and then it works, but
> I'm curious, why we can `order by x`, but not `order by x <> 'a'` ?

ORDER BY output-column-alias is a messy hangover from SQL92.
In later SQL versions, identifiers in ORDER BY have the same
interpretation as in WHERE and other clauses, namely they are
columns sourced by the FROM clause.  Supporting both interpretations
makes for a lot of ambiguity, so we only allow the old interpretation
in exactly the case required by SQL92, namely "ORDER BY identifier".

This is documented ...

            regards, tom lane