How can I achieve the same Postgres mapping behavior on Linux as on Mac OS?

Question

When switching between Mac OS (development) on Linux (production server), the sorting for Postgres changes because the sorting in Postgres depends on the configuration of the operating system.

How can I achieve the same Postgres mapping behavior on Linux as on Mac OS?

I am running Postgres 9.2

Answer 1

Locales on OS X are quite broken, and on Linux (glibc) not enough, so you will have difficulty with sorting them successfully. The only way is to use the lowest common denominator, which is the language version of C. But if you are developing an application that actually depends on the useful mapping behavior in natural language, you need to do it on the same platform that you use in production. I suggest setting up a virtual box under Linux on your OS X machine.

Answer 2

You can control the sort used when you are CREATE DATABASE - see the LC_COLLATE parameter on CREATE DATABASE .

PostgreSQL will use standard operating system mapping procedures, so you cannot always get an exact match if the OS does not offer exactly the same locales or their implementations are different. Adding support using the ICU to perform internal OS-independent sorting has long been on the wish list, but it's a huge job that no one has wanted to do enough yet.