PostgreSQL

Changing the Default Encoding of new Databases to UTF-8
When creating a new database (e.g. with ) 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 default encoding of new databases, one of the options is to change on-site template1. To do this, log into PostgresSQL shell and execute the following:

1. First we need to drop template1. As templates cannot be dropped, we first need to change it to an ordinary database: UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1'; 2. After that, it is possible to 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 we need to change template1 back to the template: UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1'; 5. (OPTIONAL) If you do not 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:

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 ---+--+---+---+---+-- mydb     | 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     |