How to force Windows to support not supported LC_COLLATE and LC_TYPE in PostgreSQL?

Question

I have fresh installation of PostgreSQL 9.1.x. Unfortunately, I discovered that OS doesn't support LC_COLLATE = 'pl_PL.utf8' and LC_CTYPE = 'pl_PL.utf8'. I read in PostgreSQL documentation that:

On all platforms, the collations named default, C, and POSIX are available. Additional collations may be available depending on operating system support.

Is a way, however to force Windows to support that locale ? I mean a patch, plugin or something.

Answer 1

On Windows, it's likely that the needed locale is already installed, it's just named differently than on Unix.

As an example, the following database creation appears to works fine for me with PG9.1 on my Windows XP, with its default locale French_France.1252 and no additional language pack installed that I'd recall.

CREATE DATABASE pldb
       ENCODING = 'UTF8'
       LC_COLLATE = 'Polish'
       LC_CTYPE = 'Polish'
       TEMPLATE=template0;

Also, a quick test of the sort order by submitting this query in UTF8 in this newly created database:

select * from (values ('z'), ('ź'), ('ż'), ('s'), ('ś'), ('x'), ('y')) as letters order by 1;

seems to output correct results:

s
ś
x
y
z
ź
ż

Answer 2

Not as far as I know, you can't force the Windows version of PostgreSQL to support locales and collations not offered by Windows. PostgreSQL uses the system locale and collation support. If the system doesn't support it, you can't use it. This is a frustrating limitation at times, but how it is. You need to pick a suitable encoding and collation supported by your Windows install.

If you want to alias an OS collation name and call it pl_PL.utf8 you may be able to do that with CREATE COLLATION.

See Character set support, Collation Support and the pg_collation catalog table.

What's the problem you're trying to solve with this?