Collation so ordering doesn't work properly with postresql

Question

I am developing a rails app which has its content in Turkish. I am using Postgresql 9.2.2 as my database backend. Everything works fine (no weird character issues etc. ) except for proper ordering.

For example, when I try to list some items that are ordered by city they are in, I expect something like "Adana, Bursa, İstanbul, Giresun, Zonguldak ..".

Instead, I always get Turkish specific characters at the end/beginning of the list. (i.e."Adana, Bursa, Giresun, Zonguldak, İstanbul")

I have initialized my postgres db with command: initdb /usr/local/var/postgres -E utf8 --locale=tr_TR

when I \l in psql console I get the expected.

 Name              Owner         Encoding   Collate   Ctype
 ----------------+-------------+----------+---------+-------+ 
 app_development | app         | UTF8     | tr_TR   | tr_TR |
 app_production  | app         | UTF8     | tr_TR   | tr_TR |
 app_test        | app         | UTF8     | tr_TR   | tr_TR |
 postgres        | monkegjinni | UTF8     | tr_TR   | tr_TR |

I also tried to create databases manually with LC_CTYPE="tr_TR.UTF-8" and LC_COLLATE="tr_TR.UTF-8", and again no progress.

Some information about my development environment:

• Running Mountain Lion 10.8.2 with Macbook Pro 7.1

• psql --version : 9.2.2

• rails --version : 3.2.11

$ locale :

• LANG="tr_TR.UTF-8"
• LC_COLLATE="tr_TR.UTF-8"
• LC_CTYPE="tr_TR.UTF-8"
• LC_MESSAGES="tr_TR.UTF-8"
• LC_MONETARY="tr_TR.UTF-8"
• LC_NUMERIC="tr_TR.UTF-8"
• LC_TIME="tr_TR.UTF-8"
• LC_ALL=

How can I resolve this issue?

Answer 1

UTF-8 locales on OS X are broken. You can try to use a non-UTF-8 locale (tr_TR.ISO8859-9), but it appears that it is also broken in this respect. So it's not going to work.