Note: Written by Oleg Bartunov. See Oleg's web page for additional information on locale and Russian language support.
People often complain that locale doesn't work for them. There are several common mistakes:
Didn't properly configure postgresql before compilation. You must run configure with --enable-locale option to enable locale support. Didn't setup environment correctly when starting postmaster. You must define environment variables LC_CTYPE and LC_COLLATE before running postmaster because backend gets information about locale from environment. I use following shell script (runpostgres):
#!/bin/sh export LC_CTYPE=koi8-r export LC_COLLATE=koi8-r postmaster -B 1024 -S -D/usr/local/pgsql/data/ -o '-Fe'and run it from rc.local as
/bin/su - postgres -c "/home/postgres/runpostgres"
Broken locale support in OS (for example, locale support in libc under Linux several times has changed and this caused a lot of problems). Latest perl has also support of locale and if locale is broken perl -v will complain something like:
8:17[mira]:~/WWW/postgres>setenv LC_CTYPE not_exist 8:18[mira]:~/WWW/postgres>perl -v perl: warning: Setting locale failed. perl: warning: Please check that your locale settings: LC_ALL = (unset), LC_CTYPE = "not_exist", LANG = (unset) are supported and installed on your system. perl: warning: Falling back to the standard locale ("C").
Wrong location of locale files! Possible locations include: /usr/lib/locale (Linux, Solaris), /usr/share/locale (Linux), /usr/lib/nls/loc (DUX 4.0). Check man locale to find the correct location. Under Linux I did a symbolic link between /usr/lib/locale and /usr/share/locale to be sure that the next libc will not break my locale.
You can use ~* and order by operators for strings contain characters from national alphabets. Non-english users definitely need that. If you won't use locale stuff just undefine the USE_LOCALE variable.
There is one evident drawback of using locale - its speed! So, use locale only if you really need it.