PostgreSQL – Change Default Encoding of New Databases To UTF-8 (Optional)

julley

　　
PostgreSQL – Change Default Encoding of New Databases To UTF-8 (Optional)
　　

When creating a new database (e.g. with createdb blog) PostgreSQL actually copies a template database. There are two predefined templates: template0 is vanilla, while template1 is meant as an on-site template changeable by the administrator and is used by default. In order to change the encoding of new database, one of the options is to change on-site template1. To do this, log into PostgresSQL shell (psql) and execute the following:
1. First, we need to drop template1. Templates can’t be dropped, so we first modify it so it’s an ordinary database:

UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
2. Now we can drop it:

DROP DATABASE template1;
3. The next step is to create a new database from template0, with a new default encoding:

CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';
4. Now modify template1 so it’s actually a template:

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
5. (RECOMMENDED) PostgreSQL documentation advises to VACUUM FREEZE the template:

\c template1
VACUUM FREEZE;
6. (OPTIONAL) If you don’t want anyone connecting to this template, set datallowconn to FALSE:

UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template1';
Now you can create a new database by running from regular shell:

su -
su - postgres
createdb blog;
If you log in back to psql and check the databases, you should see the proper encoding of your new database:

\l
returns

                              List of databases
Name    |  Owner   | Encoding  | Collation | Ctype |   Access privileges
-----------+----------+-----------+-----------+-------+----------------------
blog      | postgres | UTF8      | C         | C     |
postgres  | postgres | SQL_ASCII | C         | C     |
template0 | postgres | SQL_ASCII | C         | C     | =c/postgres
 : postgres=CTc/postgres
template1 | postgres | UTF8      | C         | C     |


　　

　　PostgreSQL – ArchWiki.
　　from: http://journal.tianhao.info/2010/12/postgresql-change-default-encoding-of-new-databases-to-utf-8-optional/#comment-1060