Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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).

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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