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
分享到:
相关推荐
This release contains a variety of fixes from 14.0. For information about new features in major release 14, see Section E.2. A dump/restore is not required for those running 14.X. However, note that...
Beginning Databases With PostgreSQL - From Novice To Professional(2nd) 英文无水印pdf 第2版 pdf所有页面使用FoxitReader和PDF-XChangeViewer测试都可以打开 本资源转载自网络,如有侵权,请联系上传者或...
postgresql-13.2-1-windows-x64.rar postgresql-13.2-1-windows-x64.rar postgresql-13.2-1-windows-x64.rar postgresql-13.2-1-windows-x64.rar postgresql-13.2-1-windows-x64.rar
PostgreSQL(postgresql-14.2-2-windows-x64.exe),适用于Windows系统:PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系开发的POSTGRES,4.2版本为基础的对象...
postgresql-10.16-2-windows-x64
postgresql-10.5-2 postgresql-10.5-2-windows-x64.exe postgresql_10.5
postgresql-12.4-1-windows-x64.exe
PostgreSQL(postgresql-13.5-1-windows-x64.exe)适用于Windows x86-64 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系开发的POSTGRES,4.2版本为基础的...
postgresql-11.8-1-windows-x64-binaries.zip
PostgreSQL安装包(Win) PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系开发的POSTGRES,4.2版本为基础的对象关系型数据库管理系统。POSTGRES的许多领先概念...
postgresql-9.5.3-1-windows-x64,windows64位版本,安装后自带pgAdmin图形管理工具
PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系开发的POSTGRES,4.2版本为基础的对象关系型数据库管理系统。POSTGRES的许多领先概念只是在比较迟的时候才出现...
postgresql-9.1.1-1-windows-binaries
官方稳定版本 postgresql-9.4.26-1-windows-x64.exe
postgresql-10.21-1-windows-x64.exe安装包
PostgreSQL(postgresql13-devel-13.5-1PGDG.rhel7.x86_64.rpm)适用于RHEL/CentOS/Oracle Linux 7 - x86_64 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系...
postgresql的如下版本postgresql-10.16-1-windows-x64.rar
postgresql-13.3-2-windows-x64 windows安装器 postgresql-13.3-2-windows-x64 windows安装器 postgresql-13.3-2-windows-x64 windows安装器 postgresql-13.3-2-windows-x64 windows安装器 postgresql-13.3-2-...
PostgreSQL-Up-and-Running-A-Practical-Guide-to-the-Advanced-Open-Source-Database,数据库课本 如题所示 全本pdf版本
PostgreSQL 10.12.1