Обсуждение: Locale and UTF8 for template1 in 8.4.4

Hi,

I'd like to change the template1 database to be in UTF8 and en_GB, so that all databases I create (unless specified
otherwise)will start with that encoding by default, if I understand correctly. I've searched around for how to do this
butcan't seem to get what I've found to work. 

If I run `locale` at the command-line, this is the output:

    LANG=
    LC_COLLATE="C"
    LC_CTYPE="C"
    LC_MESSAGES="C"
    LC_MONETARY="C"
    LC_NUMERIC="C"
    LC_TIME="C"
    LC_ALL=

I copied the template1 create table statement from the pgadmin gui, and change it from this:

    CREATE DATABASE template1
      WITH OWNER = _postgres
           ENCODING = 'SQL_ASCII'
           TABLESPACE = pg_default
           LC_COLLATE = 'C'
           LC_CTYPE = 'C'
           CONNECTION LIMIT = -1;
    GRANT CONNECT ON DATABASE template1 TO public;
    GRANT ALL ON DATABASE template1 TO _postgres;
    COMMENT ON DATABASE template1 IS 'default template database';

to this:

    CREATE DATABASE template1
      WITH OWNER = _postgres
           ENCODING = 'UTF8'
           TABLESPACE = pg_default
           LC_COLLATE = 'en_GB.UTF8'
           LC_CTYPE = 'en_GB.UTF8'
           CONNECTION LIMIT = -1;
    GRANT CONNECT ON DATABASE template1 TO public;
    GRANT ALL ON DATABASE template1 TO _postgres;
    COMMENT ON DATABASE template1 IS 'default template database';

but when I run it the output has these errors:

    ERROR:  cannot drop a template database
    STATEMENT:  DROP DATABASE template1;
    psql:/Volumes/RubyProjects/template1.sql:2: ERROR:  cannot drop a template database
    ERROR:  invalid locale name en_GB.UTF8
<snip>

Ok, so I can't change the template1 database that way, but I'm concerned that it says the locale name is invalid. I got
thelist of locales from here http://www.postgresql.org/docs/8.4/static/multibyte.html 

Would anyone be able to point out to me how I can get the template1 database to be utf8 and en_GB? (or US, I'm not
*that*fussed)  

I'm running postgres version 8.4.4

Any help is greatly appreciated.

Regards,
Iain

On May 6, 2011, Iain Barnett <iainspeed@gmail.com> wrote:

> Would anyone be able to point out to me how I can get the template1

> database to be utf8 and en_GB? (or US, I'm not *that* fussed)

Use the --encoding and --locale options to initdb.