Describes the available localization features from the
point of view of the administrator.

PostgreSQL supports
localization with three approaches:

Using the locale features of the operating system to
provide locale-specific collation order, number formatting,
translated messages, and other aspects.

Using explicit multiple-byte character sets defined in the
PostgreSQL server to support
languages that require more characters than will fit into a
single byte, and to provide character set recoding between
client and server. The number of supported character sets is
fixed at the time the server is compiled, and internal
operations such as string comparisons require expansion of
each character into a 32-bit word.

Single byte character recoding provides a more
light-weight solution for users of multiple, yet single-byte
character sets.

Locale support is not built into PostgreSQL by default; to enable it,
supply the --enable-locale option
to the configure script:

$ ./configure --enable-locale

Locale support only affects the server; all clients are
compatible with servers with or without locale support.

To enable messages translated to the user's preferred
language, the --enable-nls option
must be used. This option is independent of the other locale
support.

The information about which particular cultural rules to
use is determined by standard environment variables. If you
are getting localized behavior from other programs you
probably have them set up already. The simplest way to set
the localization information is the LANG variable, for example:

export LANG=sv_SE

This sets the locale to Swedish (sv) as spoken in Sweden (SE). Other possibilities might be en_US (U.S. English) and fr_CA (Canada, French). If more than one
character set can be useful for a locale then the
specifications look like this: cs_CZ.ISO8859-2. What locales are available
under what names on your system depends on what was provided
by the operating system vendor and what was installed.

Occasionally it is useful to mix rules from several
locales, e.g., use U.S. collation rules but Spanish messages.
To do that a set of environment variables exist that override
the default of LANG for a particular
category:

LC_COLLATE

String sort order

LC_CTYPE

Character classification (What is a letter? The
upper-case equivalent?)

LC_MESSAGES

Language of messages

LC_MONETARY

Formatting of currency amounts

LC_NUMERIC

Formatting of numbers

LC_TIME

Formatting of dates and times

Additionally, all of these specific variables and the
LANG variable can be overridden with
the LC_ALL environment variable.

Note: Some message localization libraries also
look at the environment variable LANGUAGE which overrides all other locale
settings for the purpose of setting the language of
messages. If in doubt, please refer to the documentation
of your operating system, in particular the gettext manual page,
for more information.

If you want the system to behave as if it had no locale
support, use the special locale C or
POSIX, or simply unset all
locale-related variables.

Note that the locale behavior of the server is determined
by the environment variables seen by the server, not by the
environment of any client. Therefore, be careful to set these
variables before starting the server. A consequence of this
is that if client and server are set up to different locales,
messages may appear in different languages depending on where
they originated.

The LC_COLLATE and LC_CTYPE variables affect the sort order of
indexes. Therefore, these values must be kept fixed for any
particular database cluster, or indexes on text columns will
become corrupt. PostgreSQL
enforces this by recording the values of LC_COLLATE and LC_CTYPE
that are seen by initdb. The
server automatically adopts those two values when it is
started; only the other LC_ categories
can be set from the environment at server startup. In short,
only one collation order can be used in a database cluster,
and it is chosen at initdb
time.

The only severe drawback of using the locale support in
PostgreSQL is its speed. So
use locale only if you actually need it. It should be noted
in particular that selecting a non-C locale disables index
optimizations for LIKE and
~ operators, which can make a huge
difference in the speed of searches that use those
operators.

If locale support doesn't work in spite of the explanation
above, check that the locale support in your operating system
is correctly configured. To check whether a given locale is
installed and functional you can use Perl, for example. Perl has also support
for locales and if a locale is broken perl
-v will complain something like this:

Check that your locale files are in the right location.
Possible locations include: /usr/lib/locale (Linux, Solaris), /usr/share/locale (Linux), /usr/lib/nls/loc (DUX 4.0). Check the locale man page of
your system if you are not sure.

Check that PostgreSQL is
actually using the locale that you think it is. LC_COLLATE and LC_CTYPE
settings are determined at initdb time and cannot be changed
without repeating initdb.
Other locale settings including LC_MESSAGES and LC_MONETARY are determined by the environment
the postmaster is started in, and can be changed with a
simple postmaster restart. You can check the LC_COLLATE and LC_CTYPE
settings of a database with the contrib/pg_controldata utility program.

Client applications that handle server-side errors by
parsing the text of the error message will obviously have
problems when the server's messages are in a different
language. If you create such an application you need to
devise a plan to cope with this situation. The embedded SQL
interface (ecpg) is also
affected by this problem. It is currently recommended that
servers interfacing with ecpg applications be configured to send
messages in English.

Maintaining catalogs of message translations requires the
on-going efforts of many volunteers that want to see
PostgreSQL speak their
preferred language well. If messages in your language is
currently not available or fully translated, your assistance
would be appreciated. If you want to help, refer to the
Developer's Guide or write to the
developers' mailing list.