Thread: 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.
On 6 May 2011, at 16:19, Alan Hodgson wrote:
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.
I had to backup the PGDATA directory, but it worked. This is what I ran:
initdb --encoding=UTF8 --locale=en_GB -D /path/to/data/dir
Thanks for the help, it is much appreciated.
Regards,
Iain