From: Tom Lane <tgl@sss.pgh.pa.us>
To: depesz@depesz.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: order by alias - doesn't work sometimes?
Date: Tue, 14 Oct 2025 11:10:31 -0400
Message-ID: <959907.1760454631@sss.pgh.pa.us> (raw)
In-Reply-To: <aO5jUGlQgBBAOp4_@depesz.com>
References: <aO5jUGlQgBBAOp4_@depesz.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, depesz@depesz.com, pgsql-bugs@lists.postgresql.org
Subject: Re: order by alias - doesn't work sometimes?
In-Reply-To: <959907.1760454631@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox