I want build new postgreSQL database by:
CREATE DATABASE newdb WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'zh_CN.UTF-8' CONNECTION LIMIT = -1; and the error is:
ERROR: new collation (zh_CN.UTF-8) is incompatible with the collation of the template database (en_US.UTF8)
HINT: Use the same collation as in the template database, or use template0 as template.
How to change the template database collection?
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'; 
template0? I can set datistemplate=false and drop it as well, so if I recreated it how would I configure it to allow creating a database with different encoding and locale from it?template0 that cause pg to allow different encoding and locale...Tomas answer is correct, but it is missing an important detail (LC_CTYPE). Here is the full sequence for recreating template1 with the correct locale:
ALTER database template1 is_template=false; DROP database template1; CREATE DATABASE template1 WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'zh_CN.UTF-8' LC_CTYPE = 'zh_CN.UTF-8' CONNECTION LIMIT = -1 TEMPLATE template0; ALTER database template1 is_template=true; then you can create new databases.
How to change the template database?
Access privileges are not set. REVOKE CONNECT, TEMPORARY ON DATABASE template1 FROM PUBLIC; GRANT CONNECT ON DATABASE template1 TO PUBLIC; is missing.