I'll submit a bug report for this, though I'm on the fence as to wether this is actually a bug per se.. I would have reasonably expected my original syntax to have worked (as it does without any ORDER BY)..
I seem to be having problems constructing a query that combines the use of distinct, order by, and collate key words.
For instance:
# 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... ^
How about:
select distinct(value) collate "C" from properties order by 1 ;
Am I just being a numpty here? I can work around it with a subquery, but this seems like a bug to me. Particularly the first example where my ordinal field reference is treated as an integer literal. I should note that the field 'value' is of type 'text' (not integer).
