PostgreSQL – Change Default Encoding of New Databases To UTF-8 (Optional)
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 template1VACUUM 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 - postgrescreatedb 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/postgrestemplate1 | 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
