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. postgres=#
Create user (if you haven’t done this yet):
postgres=# CREATE ROLE myuser WITH PASSWORD 'secret' LOGIN; CREATE ROLE postgres=#
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; CREATE DATABASE postgres=#
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.