'P 0' < 'P! ' in python and postgresql

Question

A script in Python didn't work, and I reduced the problem to what follows.

In PostgreSQL 9.1 I tried:

SELECT 'P 0' < 'P! '
f

And in Python 2.7.3:

>>> 'P 0' < 'P! '
True

Why is ' ' not lower than '!' in PostgreSQL? What is happening?

Answer 1

PostgreSQL is using your locale's collation rules for string comparison. Python is using a different locale (possibly "C") for collation.

It's hard to say more without knowing what your database LC_COLLATE is (from \l+ in psql) and what your runtime environment for Python is. Try showing the database locale and the output of the shell locale command.

See the PostgreSQL documentation on locales.

For example, compare and contrast:

-- results may vary depending on your OS/libc
SELECT 'P 0' < 'P! ' COLLATE "C"; -- returns true
SELECT 'P 0' < 'P! ' COLLATE "en_GB"; -- returns false

Answer 2

You can never assume that the string comparison rules are the same in two different languages or systems. There are a myriad of conventions; it's not well-defined like it is with numbers. Equality is the best-defined, but even there you find differences (trailing spaces being ignored, case not mattering, etc.); the specific ordering of unequal strings is way outside the realm of the portable.