I have a problem with ordering with provided COLLATE value on local machine (on server everything works fine). Seems like it have no any effect.
Example SQL code with en_US
COLLATE:
SELECT username
FROM "user"
WHERE (
"user"."username" IN (
'aaa',
'aab',
'aac',
'a.aa',
'a.ab',
'a.ac')
ORDER BY "user"."username" COLLATE "en_US" ASC;
Output:
a.aa
a.ab
a.ac
aaa
aab
aac
The same SQL query with C
COLLATE returns the same output:
a.aa
a.ab
a.ac
aaa
aab
aac
But on server it returns different answers for different COLLATE:
for en_US
:
aaa
aab
aac
a.aa
a.ab
a.ac
for C
:
a.aa
a.ab
a.ac
aaa
aab
aac
Local machine: OSX, Postgres 9.6
SHOW LC_COLLATE;
lc_collate
-------------
en_US.UTF-8
(1 row)
Server machine: Ubuntu, Postgres 9.6
SHOW LC_COLLATE;
lc_collate
------------
en_US.utf8
(1 row)
There is a different between SHOW LC_COLLATE;
query outputs, by they both seems valid (or not?).
What problem can be here?