I have a postgresql DB with Thai and English values/strings in several tables.

Currently I have the following settings

| Encoding |   Collate   |    Ctype |
| UTF8     | en_US.UTF-8 | en_US.UTF-8  |

If I order by a column with Thai values, the sort order is not correct.

What settings should I change to have postgresql sort correctly?

  • 1
    In future you really need to provide: Your PostgreSQL version (SELECT version()) and an example showing what's happening, e.g. a create table, a couple of inserts, a select, the results you get, and the results you think you should get. – Craig Ringer May 29 '14 at 10:57

On PostgreSQL 9.1 and newer, you can use the COLLATE qualifier on an operation to override the database's default collation. See the manual for information on collation support.


SELECT a, b FROM mytable ORDER BY c COLLATE 'th_TH.UTF-8';

Note that PostgreSQL can't mix different collations, using a dynamic collation based on detected language. It doesn't work like that.

On prior versions you must use a single database-wide collation. So you'd have to dump your database, CREATE DATABASE ... ENCODING 'UTF-8' LC_COLLATE 'th_TH.UTF-8', and re-load.

  • Thanks for the suggestion. Decided on a different tack, see my answer – Jo Erlang May 30 '14 at 4:52

Since the DB has both Engliah and Thai I decided to change the collation for the columns.

ALTER TABLE books ALTER thai_title TYPE varchar(255) COLLATE "C";

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.