I'm designing a database which will store data in different languages (using UTF-8), so I think the best way to display the query's results is ordering it according to the user's language during the query itself (because there are more than one correct ways to do that), as follows:

SELECT a < b COLLATE "de_DE" FROM test1;

Assuming this is the correct way to work with international data, which is the best collation for the database itself? PostgreSQL documentation says:

The C and POSIX collations both specify "traditional C" behavior, in which only the ASCII letters "A" through "Z" are treated as letters, and sorting is done strictly by character code byte values.

I think this is the best choice in this case, or am I wrong?

(Bonus question: is it too slow to select the collation in the query itself?).

  • 2
    The biggest pain point you're going to suffer is that in a multi-language DB you need a lot of indexes, since indexes on collatable text are collation-specific. If you tend to search only within a partiular collation/language you can use partial indexes to help keep index size under control, though. Commented Jul 16, 2013 at 0:49
  • 2
    When quoting a source, add a link. Commented Jul 16, 2013 at 2:37

3 Answers 3


The C collation is the right choice.

Everything is a bit faster without locale. And since no collation is right anyway, create the database without collation, meaning with C.

It may be a pain to have to provide a collation for many operations. There shouldn't be a noticeable difference in speed between the default collation and an ad-hoc collation, though. After all it's just unsorted data, and collation rules are applied when sorting.

Be aware that Postgres builds on the locale settings provided by the underlying OS, so you need to have locales generated for each locale to be used. More in related answer on SO here and here.

However, as @Craig already mentioned, indexes are the bottleneck in this scenario. The collation of the index has to match the collation of the applied operator in many cases that involve character data.

You can use the COLLATE specifier in indexes to produce matching indexes. Partial indexes may be the perfect choice if you are mixing data in the same table.

For example, a table with international strings:

   string_id serial
  ,lang_id   int NOT NULL
  ,string    text NOT NULL

And you are mostly interested in one language at a time:

FROM   string
WHERE  lang_id = 5  -- 5 being German / Germany here
AND    string > 'foo' COLLATE "de_DE"
ORDER  BY string COLLATE "de_DE";

Then create partial indexes like:

CREATE INDEX string_string_lang_id_idx ON string (string COLLATE "de_DE")
WHERE lang_id = 5;

One for each language you need.

Actually, inheritance might be a superior approach for a table like this. Then you can have a plain index on each inherited table containing only strings for a single locale. You need to be comfortable with the special rules for inherited tables, of course.

  • 1
    Do you use the C locale (or 'non locale' to be precise) by default for any new database? Commented Dec 12, 2014 at 8:49
  • 1
    @JackDouglas: No, I would only do that for special cases. Typically it's much more practical to work with the generally used locale at the place. Commented Dec 12, 2014 at 9:56

I suggest you pick a collation that provides the default Unicode ordering. That way, you get sane results even if you don't override the collation in each query. Unfortunately, most (all?) operating systems don't provide a locale that is simply named "default Unicode" or something like that, so you will have to guess and/or research a good choice. For example, on Linux/glibc, the de_DE.utf8 or en_US.utf8 locales simply pass through the default behavior, so both of those are good choices.

I don't think using the C locale is a good idea, because then the default behavior of your application will be useless. And you might not get proper behavior from case conversion operations.

(Overriding the collation in a query doesn't have much overhead. It's just a parse-time operation.)

  • Probably less pain to have a sane default .. Commented Jul 16, 2013 at 17:19
  • 2
    Currently I'm using es_CL.utf8 in a test database, but thanks to your answer I looked a little bit more and found that utf8_unicode_ci is the way to go.
    – Tae
    Commented Jul 17, 2013 at 18:49

We use postgres in a docker container, thus we always have ICU available and use und-x-icu as default.

This is mentioned in chapter ICU collations of the postres docs mentions:

und-x-icu (for “undefined”)
ICU “root” collation. Use this to get a reasonable language-agnostic sort order.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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