Postgresql 9.4.6 odd sorting behavior on OSX vs Linux

Question

When I run this on linux and Mac OSX with Postgres 9.4.6 I get different ordering. Is this a bug in Postgres?

CREATE TEMPORARY TABLE SORTER ( name VARCHAR );
INSERT INTO SORTER (name) VALUES ('A B'); 
INSERT INTO SORTER (name) VALUES ('AB'); 
INSERT INTO SORTER (name) VALUES ('A C'); 
INSERT INTO SORTER (name) VALUES ('AC'); 
SELECT * FROM SORTER ORDER BY name; 
SELECT * FROM SORTER ORDER BY name DESC;

OSX:

name
------
 A B
 A C
 AB
 AC
(4 rows)

 name
------
 AC
 AB
 A C
 A B
(4 rows)

Linux:

name 
------
AB
A B
AC
A C
(4 rows)

name 
------
A C
AC
A B
AB
(4 rows)

Looks like this has to do with the LOCALE in which you are running, so it's a glibc issue not postgresql... bytes.com/topic/postgresql/answers/… — Munir
– Munir, Commented Feb 17, 2016 at 22:13
Yep appears to be using en_US locale for Linux and C for OS X. — Kurt Stutsman
– Kurt Stutsman, Commented Feb 17, 2016 at 22:15

babsher · Accepted Answer · 2016-02-17 22:26:52Z

1

The problem is the LC_CTYPE on the schema is UTF-8. For some reason on mac it is set to C.

To fix it you can add a collate 'C' option to the Linux side.

answered Feb 17, 2016 at 22:26

babsher

1,0162 gold badges8 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Over a year ago

This saved us a LOT of time. Thank you! Here's another great article that explains the importance of collation as it pertains to sorting things and locales: dba.stackexchange.com/questions/94887/…

Collectives™ on Stack Overflow

Postgresql 9.4.6 odd sorting behavior on OSX vs Linux

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related