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 ?


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.

  • Dropping the cluster to modify the locale was exactly what I was looking for, thanks :) – wildpeaks Jan 10 '11 at 23:19
  • Man, after a lot of digging, that was what a was looking for too! Nothing else could make PG working with Ubuntu locale, even changing it. But re-creating the cluster with UTF8 did the trick :). Cheers! – lucasarruda Dec 18 '13 at 16:03
  • You may want to use --locale=C.UTF-8 (list supported locales on your system with locale -a). – tricasse Dec 20 '15 at 4:06

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
  • I think it worked for me! – alfonx Jan 6 '15 at 22:25
  • dpkg-reconfigure requires sudo... – Cerin Apr 19 '17 at 17:51

I think you mean:

createdb test42 --encoding utf-8

That should do what you want.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.