Setting up a locale in PostgreSQL on Linux

After installation of fresh copy of PostgreSQL database server it is often required to configure proper localization settings. This helps to avoid messages like this:

ERROR: invalid locale name en_GB.utf8

where en_GB.utf8 is setting you want to have. In order to create a database with custom locale there are few steps one has to follow.

The first is to enable all locales you might want to use in PostgreSQL in your system config. On Debian, edit file /etc/locale.gen and uncomment lines with your locales names. In my case it looks like this:

# pl_PL ISO-8859-2
pl_PL.UTF-8 UTF-8
# ps_AF UTF-8

Then run command locale-gen which will generate system localization files.

# locale-gen
Generating locales (this might take a while)...
en_US.UTF-8... done
pl_PL.UTF-8... done
Generation complete.

I have two locales enabled: en_US.UTF-8 and pl_PL.UTF-8. Both of them should be available in PostgreSQL. To check that, switch user to postgres and run SQL console, psql:

# su - postgres
$ psql
psql (8.4.9)
Type "help" for help.


Create user (if you haven’t done this yet):

postgres=# CREATE ROLE myuser WITH PASSWORD 'secret' LOGIN;

Then create database with some extra parameters:

postgres=# CREATE DATABASE mydb WITH OWNER myuser ENCODING 'UTF8' LC_COLLATE 'pl_PL.utf8' LC_CTYPE 'pl_PL.utf8' TEMPLATE template0;

Most of this is self-explanatory. Purposes of all parameters are explained in the documentation: CREATE DATABASE. Note, that there is template0 database used as a template. More on this you will find in the manual: Template Databases.

Note: above example was successfully tested also on PostgreSQL 9.1.2.

08. January 2012 by resset
Categories: Software | Tags: , , , | Leave a comment

Leave a Reply

Required fields are marked *