18th October 2018: PostgreSQL 11 Released!
Unsupported versions: 6.3
Prev Chapter 19. Runtime Environment Next

Locale Support

NOTE: Written by Oleg Bartunov. See Oleg's web page for additional information on locale and Russian language support.

While doing a project for a company in Moscow, Russia, I encountered the problem that postgresql had no support of national alphabets. After looking for possible workarounds I decided to develop support of locale myself. I'm not a C-programer but already had some experience with locale programming when I work with perl (debugging) and glimpse. After several days of digging through the Postgres source tree I made very minor corections to src/backend/utils/adt/varlena.c and src/backend/main/main.c and got what I needed! I did support only for LC_CTYPE and LC_COLLATE, but later LC_MONETARY was added by others. I got many messages from people about this patch so I decided to send it to developers and (to my surprise) it was incorporated into postgresql distribution.

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):

         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 location: /usr/lib/locale (Linux, Solaris), /usr/share/locale (Linux), /usr/lib/nls/loc (DUX 4.0) Check man locale for right place. Under Linux I did a symbolical link between /usr/lib/locale and /usr/share/locale to be sure next libc will not break my locale.

What are the Benefits?

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

What are the Drawbacks?

There is one evident drawback of using locale - it's speed ! So, use locale only if you really need it.

Prev Home Next
Runtime Environment Up Starting postmaster