postgresql无法建UFT8编码数据库解决方法

一、查看模板库编码
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding  |   Collate   |    Ctype    |   Access privileges   
-----------+----------+-----------+-------------+-------------+-----------------------
 postgres  | postgres | SQL_ASCII | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | SQL_ASCII | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |           |             |             | postgres=CTc/postgres
 template1 | postgres | SQL_ASCII | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |           |             |             | postgres=CTc/postgres
(3 rows)


二、新建数据库指定编码为UTF8

postgres=# create database www ENCODING='UTF8';
ERROR:  new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)
HINT:  Use the same encoding as in the template database, or use template0 as template.
postgres=# ^C


三、修改template0 属性
postgres=# \c - postgres

postgres=#  update pg_database set datallowconn = TRUE where datname = 'template0';
UPDATE 1
postgres=# \c template0
You are now connected to database "template0" as user "postgres".

template0=# update pg_database set datistemplate = FALSE where datname = 'template1';
UPDATE 1

template0=# drop database template1;
DROP DATABASE

template0=# create database template1 with template = template0 encoding = 'UTF8';
CREATE DATABASE

template0=# update pg_database set datistemplate = TRUE where datname = 'template1';
UPDATE 1
template0=#  \c template1

You are now connected to database "template1" as user "postgres".
template1=#  update pg_database set datallowconn = FALSE where datname = 'template0';
UPDATE 1

四、新建数据库指定编码为UTF8
template1=# create database www ENCODING='UTF8';
CREATE DATABASE
template1=# \l
                                  List of databases
   Name    |  Owner   | Encoding  |   Collate   |    Ctype    |   Access privileges   
-----------+----------+-----------+-------------+-------------+-----------------------
 postgres  | postgres | SQL_ASCII | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | SQL_ASCII | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |           |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8      | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 www       | postgres | UTF8      | zh_CN.UTF-8 | zh_CN.UTF-8 | 
(4 rows)


template1=#