How do I create Unicode databases in PostgreSQL 8.4?

Question

I installed the postgresql-8.4 package with default options. Everything worked fine, however I can't seem to manage to create unicode databases:

-- This doesn't work
createdb test1 --encoding UNICODE

-- This works
createdb test2

The error message,

createdb: database creation failed: ERROR:  new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII) 

is a bit puzzling because (afaik) I don't use a template for creating the new db, or is it implicitely referring to the default "postgres" database for some reason ?

Or maybe I'm missing a setting in a .conf file ?

Answer 1

The template it is referring to is template1, which is implicitly used if you don't specify another template.

The quickest workaround is that you create your database from template0 instead, using the createdb --template=template0.

You may wish to drop and reinitialize your entire cluster with a more sensible locale. You have probably had your operating system set to use the C locale by default. You can reinitialize the database system with these steps:

sudo pg_dropcluster --stop 8.4 main
sudo pg_createcluster --locale=en_US.utf8 --start 8.4 main

Use whatever locale you like, of course.

Answer 2

maybe you need configure the locale before to create the cluster

export LANGUAGE=en_US.UTF-8
export LANG=en_US.UTF-8
export LC_ALL=en_US.UTF-8
locale-gen en_US.UTF-8
dpkg-reconfigure locales

Answer 3

I think you mean:

createdb test42 --encoding utf-8

That should do what you want.