On ons, 2011-08-03 at 21:02 +0000, Matthias Kurz wrote: > SELECT > a.companyname AS a_companyname, > a.street, > a.zip, > a.city, > a.country > FROM > 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 works but SELECT a AS x FROM foo ORDER BY x + 1 doesn't. 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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
