On ons, 2011-08-03 at 21:02 +0000, Matthias Kurz wrote:
> a.companyname AS a_companyname,
> myAddress a
> ORDER BY
> a_companyName COLLATE "C" DESC,
> a.street COLLATE "C" ASC,
> a.zip COLLATE "C" ASC,
> a.city COLLATE "C" ASC,
> a.country COLLATE "C" ASC
> Gives me following error:
> ERROR: column "a_companyname" does not exist
> LINE 26: a_companyName COLLATE "C" ASC,
ORDER BY can only refer to output columns by themselves, not as part of
an expression. This is the same issue that
SELECT a AS x FROM foo ORDER BY x
SELECT a AS x FROM foo ORDER BY x + 1
What is perhaps not obvious is that (a_companyName COLLATE "C") is an
expression. The COLLATE clause is not specifically part of the ORDER BY
syntax, but a general expression.
> Is this the right behaviour?
> Or a bug?
Well, it works as designed and documented, and it is consistent with
other behaviors, as I showed. The SQL standard is sufficiently murky on
this subject, however, that I can't tell right now whether this is how
it is supposed to work. But it looks like someone researched this
carefully in the past, so probably yes.
In response to
pgsql-bugs by date
|Next:||From: John Carriel Gomez||Date: 2011-08-07 20:10:33|
|Previous:||From: Craig Ringer||Date: 2011-08-06 02:02:38|
|Subject: Re: BUG #6143: connection problem|