LC_COLLATE and LC_CTYPE support for utf-8 in postgreSQL

Question

From this link http://wiki.postgresql.org/wiki/Todo:Collate and http://www.postgresql.org/docs/9.1/static/collation.html. it's make me confused.

They said, utf-8 support depends on OS capability, and its deferent between every LC_COLLATE and LC_CTYPE options.

When I create database using pdAdmin III, there is 3 option, C, Posix and English_United States.1252. What's the different between C, Posix and English_United States.1252?

I want work with all languages supported, that is unicode utf-8, which doesn't seem to be an option.

What I must chose for the values of LC_COLLATE and LC_CTYPE for creating database that support utf-8 in PostgreSQL 9?

Answer 1

As documentation, 22.2. Character Set Support says:

An important restriction, however, is that each database's character set must be compatible with the database's LC_CTYPE (character classification) and LC_COLLATE (string sort order) locale settings. For C or POSIX locale, any character set is allowed, but for other locales there is only one character set that will work correctly. (On Windows, however, UTF-8 encoding can be used with any locale.)

I see that probably you have PostgreSQL under Windows host (English_United States.1252 locale), so you can use whatever of these (Posix is as same as C and means "no locale"), read 22.1. Locale Support:

Locale support refers to an application respecting cultural preferences regarding alphabets, sorting, number formatting, etc.

If you want the system to behave as if it had no locale support, use the special locale C or POSIX.

EDIT:

Windows platform isn't POSIX compliant, so you should use C locale for that platform. I don't know why installer has POSIX option (I guess it's rather a bug):

To create new cluster without locale you can simply use --no-locale switch e.g.:

initdb --no-locale -E UTF-8 C:\pgdata