Blog / 2011 / December / 16

Norwegian Sorting on Postgres 8.4

December 16, 2011

I wanted to create a database in postgres 8.4 that had norwegian sorting on text, so that accented characters were sorted in the correct place.

I tried this using a simple create database command, but it failed as the locale was not found.

postgres=# create database test with encoding 'utf8' lc_collate 'nb_NO';
ERROR: invalid locale name nb_NO

To see what locales are available on the system, run locale -a:

# locale -a
C
en_GB.utf8
POSIX

So in this case the Norwegian locale I want is not installed.

The file in /usr/share/i18n/SUPPORTED lists the locales that are supported, one of the lines in here is:

nb_NO.UTF-8 UTF-8

which is the one I want.

To generate this locale, add it to /etc/locale.gen. I found there was a full list of locales in this file, all commented out. Once it is added, run locale-gen:

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

I then restarted the machine, and was able to create a database in postgres with the correct sorting:

postgres=# create database test with encoding 'utf8' lc_collate 'nb_NO.utf8';
ERROR:  new collation (nb_NO.utf8) is incompatible with the collation of the template database (en_GB.UTF-8)
HINT:  Use the same collation as in the template database, or use template0 as template.

At least, I could once I used the correct template:

postgres=# create database test with encoding 'utf8' lc_collate 'nb_NO.utf8' template template0;
CREATE DATABASE

References

Tags: postgres locale