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 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.

References:
http://www.postgresql.org/docs/current/static/multibyte.html

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.