Re: Odd error when using UNION and COLLATE

Actually there's nothing about UNION here. It's true for any column alias:

::***> select 'a-c' AS x ORDER BY x COLLATE "C" ;
ERROR:  42703: column "x" does not exist
LINE 2: select 'a-c' AS x ORDER BY x COLLATE "C" ;                                  ^
LOCATION:  errorMissingColumn, parse_relation.c:2892
Time: 0.204 ms

Also you don't need WITH, just an old-fashioned inline view:

::***> select * from (select 'a-c'::text AS x) as subquery ORDER BY x
COLLATE "C" ;
┌─────┐
│  x  │
├─────┤
│ a-c │
└─────┘
(1 row)