39

I have Postgres 9.4.4 running on Debian and I get the following ORDER BY behavior:

veure_test=# show LC_COLLATE;
 lc_collate  
-------------
 en_US.UTF-8
(1 row)

veure_test=# SELECT regexp_split_to_table('D d a A c b CD Capacitor', ' ') ORDER BY 1;
 regexp_split_to_table 
-----------------------
 a
 A
 b
 c
 Capacitor
 CD
 d
 D
(8 rows)

And uname -a:

Linux ---- 3.2.0-4-amd64 #1 SMP Debian 3.2.65-1 x86_64 GNU/Linux

However, on my iMac, with Postgres 9.3.4, I get the following:

veure_test=# show LC_COLLATE;
 lc_collate  
-------------
 en_US.UTF-8
(1 row)

veure_test=# SELECT regexp_split_to_table('D d a A c b CD Capacitor', ' ') ORDER BY 1;
 regexp_split_to_table 
-----------------------
 A
 CD
 Capacitor
 D
 a
 b
 c
 d
(8 rows)

And the uname -a:

Darwin ---- 14.4.0 Darwin Kernel Version 14.4.0: Thu May 28 11:35:04 PDT 2015; root:xnu-2782.30.5~1/RELEASE_X86_64 x86_64

I'm mystified by why the Debian version appears to be case-insensitive and the OS X version is not. What am I missing, or what other information do I need to provide?

Update: On my Mac, the pg_collation table shows I have an en_US.UTF-8 collation, but on Debian, I have an en_US.utf8 collation. Thus, on my Mac:

veure_test=# with foo as (
SELECT regexp_split_to_table('D d a A c b CD Capacitor', ' ') as bar
   )
SELECT bar FROM foo
ORDER BY bar collate "en_US.UTF-8";                                                                                                                                                                                      
    bar    
-----------
 A
 CD
 Capacitor
 D
 a
 b
 c
 d
(8 rows)

And on Debian:

veure_test=# with foo as (
SELECT regexp_split_to_table('D d a A c b CD Capacitor', ' ') as bar
   )
SELECT bar FROM foo
ORDER BY bar collate "en_US.utf8";
    bar    
-----------
 a
 A
 b
 c
 Capacitor
 CD
 d
 D
(8 rows)

So en_US.UTF-8 and en_US.utf8 have different sort orders?

6
  • I don't have a Mac to test on, so I'm shooting in the dark here... Any chance that the string 'D d a A c b CD Capacitor' is not being cast as a text field on the Mac? I.E., try SELECT regexp_split_to_table('D d a A c b CD Capacitor'::text, ' ') ORDER BY 1; and see what happens...
    – Chris
    Jul 14, 2015 at 21:29
  • Same result. In other news, turns out that select * from pg_collation shows the Debian box has en_US.utf8, while the OS X has en_US.UTF-8. Using those to explicitly force collation on the respective boxes shows different sort orders :(
    – Curtis Poe
    Jul 14, 2015 at 21:36
  • And I've posted an update which might explain the problem, but for me, it merely deepens the mystery. And I've now found this: stackoverflow.com/questions/19967555/… and this: stackoverflow.com/questions/27395317/…
    – Curtis Poe
    Jul 14, 2015 at 21:39
  • 8
    Unfortunately Postgres uses the collation implementation from the OS which makes this kind of behaviour OS dependent (which I personally consider a bug - a DBMS should behave identical regardless of the OS). So this boils down to differences in the system libraries between Debian and OSX
    – user1822
    Jul 14, 2015 at 21:49
  • 2
    There will be disagreement between Postgres and other parts of the system if the sort order does not fall in line with the rest. I, too, prefer identical behavior, but I wouldn't call it a bug to follow the system locale. Ultimately, identical locales should behave identically across OS. The Debian locale seems to right, Apple seems to be at fault (unless there is some other explanation). Jul 14, 2015 at 23:13

1 Answer 1

32
+100

So en_US.UTF-8 and en_US.utf8 have different sort orders?

No, these both are the same, just a different naming convention.

I'm mystified by why the Debian version appears to be case-insensitive and the OS X version is not.

Yes, you are correct. This is the default behavior on Mac. Collations don't work on any BSD-ish OS (incl. OSX) for UTF8 encoding.

Here is a reference to prove that:

Problems with sort order (UTF8 locales don't work

As a_horse_with_no_name said, Postgres uses the collation implementation from the OS. There is no way to get the same result on both operating systems.

In your case you may(I said maybe)do like this: ORDER BY lower(fieldname).

5
  • 3
    Take care to verify performance when using ORDER BY function() on potentially large resultsets - as it stops an index being used for the sort it will almost certainly cause an extra sort operation (possibly on disc) and it may change the query planner's method of attacking your query more widely. Mar 7, 2016 at 10:05
  • @David Spillett: You are right about the Order function. I think my answer is more focused on why the OP is having different sorting fashion in iMac and Debian. Thanks
    – atokpas
    Mar 7, 2016 at 10:33
  • 1
    Yes, your answer is perfectly fine and covers the question completely. Mentioning "testing with real data after changes that might affect query plan" has become a habitual reaction in me though (much like mentioning testing in any discussion of backups, and such forth) as it is easy to forget (and people often do) or not even know to in the case of people newer to database work. Mar 7, 2016 at 11:56
  • @DavidSpillett I doesn't stop an index from being used, it only stops a plain index from being used. An index on the function result will be used if present. create index l_bar on foo ( lower(bar)) ;
    – Jasen
    Jan 22, 2020 at 3:03
  • "There is no way to get the same result on both operating systems." That's only partly correct or at least for me this statement was kind of misleading. If you set LC_COLLATE to 'C' (Ascii) the output should be the same.
    – Tim Malich
    Nov 5, 2020 at 13:08

Your Answer

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

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