The other day I was experimenting with PostgreSQL under Windows and I was very confused setting a locale in the command line when initializing the database cluster. If I didn’t specify a locale in the --locale option of initdb, then the locale that was used was: Greek_Greece.1253. Well, I needed to initialize the cluster with en_US.UTF-8, but it would not be accepted. After some experimentation, I realized that no matter what name I tried, for example en_US.UTF-8, el_GR.UTF-8 etc, it did not work out! This led me to the conclusion that Windows uses different names than those I used in Linux for the same task.

At this point, I should note that I didn’t use the PostgreSQL Windows Installer package, which would have probably taken care of the locale setting automatically or provide me with an option to customize it, but, as the advanced computer guru that I am, I preferred the bare Windows binaries package instead.

After several unsuccessful attempts, I thought of using the locale Python module and see if it could help me get a list of locale names that Windows supports. I managed to get a list of locales, but it turned out that those would not be accepted by initdb either.

As a last resort, I decided to dig into MSDN for an answer. My experience with that place is minimal, so it took me a while before familiarizing myself and making progress with my research. But finally, I managed to locate the needed information. So, Windows uses the same pattern for locale names:

language_territory.codeset

But the names for the language and territory parts are different. To get a proper name it is required to combine information from the following three pages:

1. language: Language Strings
2. territory: Country/Region Strings
3. codeset: Code Pages Supported by Windows

So, for instance, if you want to set the “en_US.UTF-8” locale in Windows, you have to use “american_usa“. It is a bit frustrating that locale names are not common among the various operating systems. I needed to do a very simple thing and this has added a lot of overhead. I am very disappointed.

As an example, I post the command line options I finally used with initdb in order to initialize the PostgreSQL database cluster:

initdb.exe --auth=md5 --locale=american_usa --encoding=UTF8 --pgdata=G:\PostgreSQL\data --xlogdir=G:\PostgreSQL\xlogs  -U postgres -W

As a general conclusion, I would say it would be a lot better if we spent our time doing more creative things than trying to match the locale names between various operating systems. I am quite sure that both Linux and Windows use names based on some kind of standard, but why can’t they be based on a single standard?

Locale in Windows by George Notaras is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.
Copyright © 2011 - Some Rights Reserved