Postgres collation on aliased field

Question

I have the following table:

CREATE TABLE public_bodies
  ("id" int, "name" varchar(46))
;

INSERT INTO public_bodies
  ("id", "name")
VALUES
  (1, 'Ytre Helgeland District Psychiatric Centre'),
  (2, 'Åfjord Municipality'),
  (3, 'Østfold Hospital')
;

I'd like to run this query:

SELECT public_bodies.id, public_bodies.name AS display_name
FROM public_bodies
ORDER BY display_name COLLATE "en_US";

But I get this error:

ERROR:  column "display_name" does not exist
LINE 3: ORDER BY display_name COLLATE "en_US";
                 ^

Ordering by the table name works fine:

SELECT public_bodies.id, public_bodies.name AS display_name
FROM public_bodies
ORDER BY public_bodies.name COLLATE "en_US";

--  id |        display_name
-- ----+--------------------------------------------
--   2 | Åfjord Municipality
--   3 | Østfold Hospital
--   1 | Ytre Helgeland District Psychiatric Centre

Ordering on the alias works okay too:

SELECT public_bodies.id, public_bodies.name AS display_name
FROM public_bodies
ORDER BY display_name;

--  id |        display_name
-- ----+--------------------------------------------
--   2 | Åfjord Municipality
--   3 | Østfold Hospital
--   1 | Ytre Helgeland District Psychiatric Centre

Applying the COLLATE before assigning the alias works, but I don't understand why this is different to collating after the ORDER_BY.

SELECT public_bodies.id, public_bodies.name COLLATE "en_US" AS display_name
FROM public_bodies
ORDER BY display_name;

--  id |        display_name
-- ----+--------------------------------------------
--   2 | Åfjord Municipality
--   3 | Østfold Hospital
--   1 | Ytre Helgeland District Psychiatric Centre

Postgres version:

SELECT version();
                                               version
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.12 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

I've get the same results on SQL fiddle (Postgres 9.3).

Why can't Postgres collate on the aliased field?

Answer 1

It's the way the language is defined. COLLATE clauses apply to expressions, and this case doesn't qualify.

By "expression", I mean some collection of operators, functions, variable identifiers, literals, etc., combined to produce an output value. In other words, the general class of value-producing "things" which are allowed to appear as a function argument, as a SELECT field definition, in a VALUES list, and so on.

A COLLATE clause may be attached to an expression, and an expression may appear in an ORDER BY list, but it's not the only thing allowed in an ORDER BY list; you can also include the names or positions of output columns, but these are treated as a distinct case by the parser.

The reason they need to be treated differently is that the query's output field identifiers are not in scope while evaluating expressions; this is why something like ORDER BY display_name || 'x' comes back with column "display_name" does not exist. To work around this, bare field names in the ORDER BY list are compared against the output list before expression evaluation is even attempted, but as a consequence, nothing more complex than a bare field name is accepted in this context (and that includes an attached COLLATE clause).