Today I Learned

A Hashrocket project

2 posts by marylee @marybethlee11

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