5

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?

1
  • 1
    The underlying operating system may need additional configuration. You may want to revisit the installation documentation. Jan 31, 2023 at 17:04

3 Answers 3

5

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');
1
  • I've tried this but it still has the same error. Any other ideas? Jan 31, 2023 at 10:46
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
1
  • Tried it on the Mac and resulted in 'test last name' going before 'test2 last name'. 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? Feb 1, 2023 at 1:16
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.