Differences in PostgreSQL 14 collation behavior on Linux and Mac

Question

I have PostgreSQL installed on my Mac and Ubuntu Server as shown below:

atsweb=# select version();
                                                      version                                                      
-------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.6 on x86_64-apple-darwin20.6.0, compiled by Apple clang version 12.0.0 (clang-1200.0.32.29), 64-bit
(1 row)

atsweb=# select version();
                                                               version                                                                
--------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.6 (Ubuntu 14.6-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0, 64-bit
(1 row)

They have the same databases, collation and encoding:

atsweb=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 atsweb    | atsweb   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

However, when I specify a en_US.UTF-8 collation on Ubuntu, I get the error:

atsweb=# select 'test last name' < 'test2 last name' COLLATE "en_US.UTF8";
ERROR:  collation "en_US.UTF8" for encoding "UTF8" does not exist
LINE 1: select 'test last name' < 'test2 last name' COLLATE "en_US.U...

Specifying en_US collation works on Ubuntu but when I run the exact same statement as shown below, the results are different:

Ubuntu:

atsweb=# select 'test last name' < 'test2 last name' COLLATE "en_US";
 ?column? 
----------
 f
(1 row)

Mac:

atsweb=# select 'test last name' < 'test2 last name' COLLATE "en_US";
 ?column? 
----------
 t
(1 row)

Is this a misconfiguration on my end or a bug somewhere? Any leads on how I can fix this?

Update:

Tried on a postgresql-14.6 docker image and yielded the exact same results as in Ubuntu. Does it mean that it's the Mac installation that has the problem? Is 'test2 last name' supposed to go before 'test last name' under "en_US" or "en_US.UTF-8" collation?

Answer 1

You might have to generate the corresponding locale on Ubuntu by running the following command as root:

locale-gen en_US.UTF-8

After that, you have to create the new collations in PostgreSQL as a superuser:

SELECT pg_import_system_collations('pg_catalog');

Answer 2

Seems like a bug in Mac's implementation of collations.

Take PostgreSQL out of the equation by doing the ordering directly in the shell. Something like this (for bash)

unset LC_ALL
echo -e "test last name\ntest2 last name"| LANG=en_US.UTF-8 sort

Answer 3

Apparently, as pointed out by this and this answer, except for "C" sorting, collation is OS-dependent and may differ even if they are named the same. There is even an open ticket pointing out that the implementation in Mac is broken. Since I only need the sorting to be consistent for testing purposes, I modified my tests to work around this issue.