How can I achieve the same Postgres collation behavior in Linux as that in Mac OS?

Question

When switching between Mac OS (development) to Linux (production server), the collation for Postgres changes since collation in Postgres is dependent on the underlying operating system config.

How can I achieve the same Postgres collation behavior in Linux as that which I have in Mac OS?

I am running Postgres 9.2

Answer 1

The locales on OS X are sufficiently broken and the ones on Linux (glibc) are sufficiently not, so that you are going to have difficulties sorting this out successfully. The only way would be to use the lowest common denominator, which is the C locale. But if you are developing an application that actually depends on useful natural-language collation behavior, you need to do it on the same platform that you use in production. I suggest setting up a virtual box running Linux on your OS X machine.

Answer 2

You can control the collation used when you CREATE DATABASE - see the LC_COLLATE option to CREATE DATABASE.

PostgreSQL will use the underlying operating system collation routines, so you can't always get an exact match if the OSes don't offer exactly the same locales or their implementations differ. Adding support for using ICU to do internal OS-independent collation has long been on the wishlist, but it's a huge job that nobody has wanted badly enough to do yet.