Seems you can't use UNION and COLLATE in the same SELECT statement; you
have to put the UNION inside of WITH and then do the COLLATE outside:
test=> SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY 1 COLLATE "C";ERROR: collations are not supported by type
integerLINE1: ... 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY 1 COLLATE "C...
^
test=> SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY x COLLATE "C";ERROR: invalid UNION/INTERSECT/EXCEPT ORDER
BYclauseLINE 1: ...CT 'a-c' AS x UNION ALL SELECT 'ab' AS x ORDER BY x COLLATE ...
^DETAIL: Only result column names can be used, not expressions or functions.HINT: Add the
expression/functionto every SELECT, or move the UNION into a FROM clause.
test=> WITH d AS (SELECT 'a-c' AS x UNION ALL SELECT 'ab' AS x) SELECT * FROM d ORDER BY x COLLATE "C"; x----- a-c
ab(2rows)
I think the 'ORDER BY x COLLATE "C"' is being parsed as an a_expr, and
we don't allow a_expr in a UNION. Perhaps we are too strict here, but I
can't tell.
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +