PostGreSQL: encoding problem when createdb and LC_ALL=C

Question

I have a script that run the following command to create a database:

createdb --template template0 -E SQL_ASCII foobar

I get the error:

createdb: database creation failed: ERROR:  encoding SQL_ASCII does not match locale en_US.UTF-8
DETAIL:  The chosen LC_CTYPE setting requires encoding UTF8.

I can understand why the locale can be a problem, so I decided to set LC_ALL=C but it doesn't work still:

$ LC_ALL=C createdb --template template0 -E SQL_ASCII gnattracker
createdb: database creation failed: ERROR:  encoding SQL_ASCII does not match locale en_US.UTF-8
DETAIL:  The chosen LC_CTYPE setting requires encoding UTF8.

Do you have an idea what the problem could be?

I am using Fedora 13

Answer 1

Thanks voretaq7, now I understand the problem.

So, what is it?

The problem is that createdb vdoesn't read the environment variable LC_ALL when a template database is used. With the flag -e we can se it is issuing the following command to the server:

CREATE DATABASE gnattracker ENCODING 'SQL_ASCII' TEMPLATE template0;

In that particular case, the locale information from the template is used for the new database. And the encoding is incompatible with these locale settings. This was probably because LC_ALL was not set to C when the databases were initialized with initdb.

The simple solution would be to change the locale settings from template0. So I tried:

ALTER DATABASE template0 SET LC_CTYPE TO 'C';
ALTER DATABASE template0 SET LC_COLLATE TO 'C';

The problem is that locale settings can't be changed at all. Bad luck. So, I resolved to clone template0 with new locale settings and replace the existing template0 with its clone:

CREATE DATABASE template0b TEMPLATE template0 LC_COLLATE 'C' LC_CTYPE 'C';
DROP   DATABASE template0;
ALTER  DATABASE template0b RENAME TO template0;

The newt thing is to tell PostGreSQL that template0 is a template and that you don't have to be the owner of the database to copy it:

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template0';

That solved my problem (see also this problem).

Answer 2

Check your other locale environment variables, and/or see http://www.postgresql.org/docs/8.4/static/locale.html for how to override the locale setting.