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 ?

3 Answers 3


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, 2011 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! Dec 18, 2013 at 16:03
  • You may want to use --locale=C.UTF-8 (list supported locales on your system with locale -a).
    – tricasse
    Dec 20, 2015 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, 2015 at 22:25
  • dpkg-reconfigure requires sudo...
    – Cerin
    Apr 19, 2017 at 17:51

I think you mean:

createdb test42 --encoding utf-8

That should do what you want.


You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .