The following bug has been logged on the website:
Bug reference: 8243
Logged by: Tim Kane
Email address: tim.kane@gmail.com
PostgreSQL version: 9.1.9
Operating system: Debian
Description:
As per psql general mailing list thread:
http://www.postgresql.org/message-id/CADVWZZJd5veDpEi_kgzBJ3uRNuE23Zs=x=az_PewssBfmA7TSA@mail.gmail.com
The following query refuses to parse:
# select distinct(value) from properties order by 1 collate "C";
ERROR: collations are not supported by type integer
LINE 1: ... distinct(value) from properties order by 1 collate "C...
^
# select distinct(value) from properties order by distinct(value) collate
"C";
ERROR: syntax error at or near "distinct"
LINE 1: ...ct distinct(value) from properties order by distinct(v...
^
# select distinct(value) as foo from properties order by foo collate "C";
ERROR: column "foo" does not exist
LINE 1: ...tinct(value) as foo from properties order by foo collat...
The solution was as follows:
# select distinct(value) collate "C" from properties order by 1;
It's arguable if this is a bug or not, though I would have expected the
first attempt at least to be valid.
Cheers,
Tim