Sample Header Ad - 728x90

Differences in PostgreSQL 14 collation behavior on Linux and Mac

5 votes
3 answers
1823 views
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?
Asked by Christian Alis (151 rep)
Jan 31, 2023, 05:04 AM
Last activity: Feb 3, 2023, 02:52 AM