How I changed the encoding of template1 after the fact

From: Kevin Murphy <murphy(at)genome(dot)chop(dot)edu>
To: PostgreSQL general <pgsql-general(at)postgresql(dot)org>
Subject: How I changed the encoding of template1 after the fact
Date: 2006-02-23 20:43:17
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-general

I wanted to change the encoding of the template1 database, but when I
tried to drop template1, I get the message, "ERROR: cannot drop a
template database".

The docs
say that this is possible, but a user comment on that page says you can't.

Actually, you *can* drop a template database, if you first convert it
into a non-template database, as per

Here's a condensed example, in which template1 is recreated to change
its default encoding:

-- Connect as the postgres superuser,

-- psql -U postgres

-- Then

UPDATE pg_database SET datallowconn = TRUE where datname = 'template0';
\c template0
UPDATE pg_database SET datistemplate = FALSE where datname = 'template1';
drop database template1;
create database template1 with template = template0 encoding = 'UNICODE';
UPDATE pg_database SET datistemplate = TRUE where datname = 'template1';
\c template1
UPDATE pg_database SET datallowconn = FALSE where datname = 'template0';

If you just wanted to clone template0, you would leave out the "encoding
= 'UNICODE'" clause.

Gurus, any corrections or safety advisories you care to make?

Kevin Murphy


Browse pgsql-general by date

  From Date Subject
Next Message Emi Lu 2006-02-23 20:45:51 Re: SQL TYPE MAP such as SQL_CHAR, SQL_NUMERIC , etc
Previous Message Carlos Henrique Reimer 2006-02-23 20:12:39 Is the pg_locks been used?