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?

  • 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 '15 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 '15 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 '15 at 21:39
  • 7
    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 – a_horse_with_no_name Jul 14 '15 at 21:49
  • 1
    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). – Erwin Brandstetter Jul 14 '15 at 23:13
up vote 12 down vote
+100

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

No, these both are 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 the default behavior in Mac. Collations will not work on any BSD-ish OS (incl. OSX) for an 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 may be)do like this: ORDER BY lower(fieldname).

  • 1
    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. – David Spillett Mar 7 '16 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 – JSapkota Mar 7 '16 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. – David Spillett Mar 7 '16 at 11:56

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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