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:

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:

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

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:

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

Then create database with some extra parameters:

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 *