How to change the template database collection coding

postgresql, postgresql-9.2

Solution

From PostgreSQL documentation:

Another common reason for copying template0 instead of template1 is that new encoding and locale settings can be specified when copying template0, whereas a copy of template1 must use the same settings it does. This is because template1 might contain encoding-specific or locale-specific data, while template0 is known not to.

You can use only `template0` to create new database with different encoding and locale:

CREATE DATABASE newdb
WITH OWNER = postgres
   ENCODING = 'UTF8'
   TABLESPACE = pg_default
   LC_COLLATE = 'zh_CN.UTF-8'
   CONNECTION LIMIT = -1
   TEMPLATE template0;

This will work, however it means that any changes you made to `template1` won't be applied to newly created database.

To change encoding and collation of `template1` you have to first delete `template1` and then create new template `template1` from `template0`. How to drop template database is described here. Then you can create new database `template1` with chosen encoding/collation and mark it as a template by setting `datistemplate=true` (example):

update pg_database set datistemplate=true  where datname='template1';

Problem

Original source

Related problems

• How to drop a template database from PostgreSQL?