[SOLVED] Postgresql ORDER_BY ... COLLATE with different values returns same output

Postgresql ORDER_BY ... COLLATE with different values returns same output

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?

Solution

PostgreSQL by default uses the collations of the operating system's C library, so the explanation is that these are different on these operating systems.

To avoid that problem, use PostgreSQL version v10 or better, built with ICU support. Then (as long as you use the same version of the ICU library) these collations will be the same across different operating systems.