Today I Learned

A Hashrocket project

Postgres locale settings are inherited from OS

By default, postgres inherits locale settings from operating system, which can greatly affect sort. Comparing linux and unix, both using the locale en_US.UTF-8, we see the following sort outputs:

Unix

select name from unnest(array['No one', 'None', ' Not']) name order by name;
name
--------
 Not
No one
None

Linux

select name from unnest(array['No one', 'None', ' Not']) name order by name;
name
--------
None
No one
 Not

You’ll notice that on the linux system, whitespaces are ignored during sorting.

To get consistent behavior across operating systems, you can use the postgres provided C locale:

select name from unnest(array['No one', 'None', ' Not']) name order by name collate 'C';
name
--------
 Not
No one
None
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.