How to change the template database collection coding

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";

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)