Locale and UTF8 for template1 in 8.4.4

[Iain Barnett <iainspeed@xxxxxxxxx>]

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 but can'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 the list 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



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general