Beginning with PostgreSQL 7.3, locale support is enabled by default. Whether it is enabled in a particular database cluster depends on how initdb was carried out.
- You called initdb with a particular locale, but initdb produced errors like this: Failed to initialize lc_monetary to ''
- When starting the postmaster (e.g. with pg_ctl) it died with messages such as the above and / or: FATAL: invalid value for option 'LC_MESSAGES': 'de_DE'
You probably specified a locale not supported by your system, even though it looks right and / or worked perfectly on a similar operating system.
For example, initdb -D /path/to/data --locale=de_DE may work on SuSE Linux but not on RedHat or FreeBSD.
Examine the system's locale directory to see exactly which locale names are supported.
You have a character with an accent or umlaut, such as ö, and wish to knock the funny bits off the top.
Use to_ascii, e.g.
SELECT to_ascii('ö', 'LATIN1')
should return o.
Note 1: currently only the encodings LATIN1, LATIN2 and WIN1250 are supported by to_ascii().
Note 2: If your data makes heavy use of extended ASCII character sets such as LATIN1 (ISO-8859-1), consider using locale settings to perform sorting operations etc.
The following statement produces results in the wrong order:
test=# SELECT lastname FROM people WHERE lastname LIKE 'M%' ORDER BY lastname; lastname ---------- Mayer Mosel Mueller Muth März Möller Müller
i.e. März should really appear between Mayer and Mosel
To provide sorting order in a particular language, PostgreSQL must be set up to use a locale appropriate for that language, e.g. de_DE for German or fr_FR for French. The locale for a particular database cluster is defined when initdb is carried out. Specifically the LC_COLLATE must be set correctly. By default it is taken from the environment of the user executing initdb, but can also be explicitly specified using initdb's --locale=LOCALE or --lc-collate=LOCALE flags.
Note that in current versions of PostgreSQL, LC_COLLATE is fixed and cannot be changed without reloading the database into a correctly initialised cluster.
Also note that the character set of a particular database is not directly connected with its locale settings.