900      阿裏雲  技術社區[雲棲]

如何設置數據庫的LC_COLLATE, LC_CTYPE, ENCODING, TEMPLATE

標簽

PostgreSQL , create database , collate , ctype , pg_encoding , pg_encoding_to_char()

背景

PostgreSQL實例支持創建多個數據庫，創建數據庫時，可以指定模板庫，並為每個數據庫設置不同的字符集、本地化collate, 貨幣等屬性。

接下來舉例講述CREATE DATABASE的具體使用方法。

一、CREATE DATABASE語法

Command:     CREATE DATABASE  
Description: create a new database  
Syntax:  
CREATE DATABASE name  
    [ [ WITH ] [ OWNER [=] user_name ]  
           [ TEMPLATE [=] template ]  
           [ ENCODING [=] encoding ]  
           [ LC_COLLATE [=] lc_collate ]  
           [ LC_CTYPE [=] lc_ctype ]  
           [ TABLESPACE [=] tablespace_name ]  
           [ CONNECTION LIMIT [=] connlimit ] ]  

二、克隆模板庫(從指定模板創建數據庫)

例子

以test數據庫為模板，克隆一個名為test01的數據庫。

test=> create database test01 with template test;  
CREATE DATABASE  

如果不指定模板，則默認的模板為template1。

注意

從指定模板庫創建數據庫，除了當前連接，不能有其他用戶連在對應的模板庫上麵。

如果有其他用戶連在test庫，可能會報類似這樣的錯誤

test=> create database test01 with template test;  
ERROR:  source database "test" is being accessed by other users  
DETAIL:  There is 1 other session using the database.  

三、創建數據庫時，如何指定encoding

PostgreSQL支持哪些encoding

用戶可以參考PostgreSQL的官方文檔，有對應的字符集支持列表

https://www.postgresql.org/docs/9.6/static/multibyte.html

Server=Yes表示該字符集支持用於create database。否則隻支持作為客戶端字符集。

創建指定encoding的數據庫

例子

創建一個UTF-8字符集的數據庫

test=> create database test02 with encoding 'UTF-8';  
CREATE DATABASE  

注意

1. 指定的字符集必須是模板庫字符集的超集，否則會報錯。

2. 指定的lc_ctype和lc_collate必須與目標字符集兼容。

例子，template1是默認模板庫，它的字符集為UTF8。

test=> \l template1  
                                  List of databases  
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges     
-----------+----------+----------+-------------+-------------+-----------------------  
 template1 | xxxxxxxx | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/xxxxxxxx          +  
           |          |          |             |             | xxxxxxxx=CTc/xxxxxxxx  
(1 row)  

創建一個EUC_CN字符集的數據庫

報錯1，EUC_CN字符集與模板庫的lc_collate,lc_ctype不兼容。

test=> create database test03 with encoding 'EUC_CN';  
ERROR:  encoding "EUC_CN" does not match locale "zh_CN.UTF-8"  
DETAIL:  The chosen LC_CTYPE setting requires encoding "UTF8".  

報錯2，EUC_CN字符集與模板庫的字符集UTF-8不兼容。

test=> create database test03 with encoding 'EUC_CN' lc_collate='C' lc_ctype='C';  
ERROR:  new encoding (EUC_CN) is incompatible with the encoding of the template database (UTF8)  
HINT:  Use the same encoding as in the template database, or use template0 as template.  

四、如何獲取字符集支持的LC_COLLATE, LC_CTYPE信息

使用如下SQL可以查詢係統表pg_collation得到字符集支持的lc_collate和lc_ctype。

其中encoding為空時，表示這個collation支持所有的字符集。

test=> select pg_encoding_to_char(collencoding) as encoding,collname,collcollate,collctype from pg_collation ;  
  encoding  |       collname        |      collcollate      |       collctype         
------------+-----------------------+-----------------------+-----------------------  
            | default               |                       |   
            | C                     | C                     | C  
            | POSIX                 | POSIX                 | POSIX  
 UTF8       | aa_DJ                 | aa_DJ.utf8            | aa_DJ.utf8  
 LATIN1     | aa_DJ                 | aa_DJ                 | aa_DJ  
 LATIN1     | aa_DJ.iso88591        | aa_DJ.iso88591        | aa_DJ.iso88591  
 UTF8       | aa_DJ.utf8            | aa_DJ.utf8            | aa_DJ.utf8  
 UTF8       | aa_ER                 | aa_ER                 | aa_ER  
 UTF8       | aa_ER.utf8            | aa_ER.utf8            | aa_ER.utf8  
.......  
 EUC_CN     | zh_CN                 | zh_CN                 | zh_CN  
 UTF8       | zh_CN                 | zh_CN.utf8            | zh_CN.utf8  
 EUC_CN     | zh_CN.gb2312          | zh_CN.gb2312          | zh_CN.gb2312  
 UTF8       | zh_CN.utf8            | zh_CN.utf8            | zh_CN.utf8  
 UTF8       | zh_HK                 | zh_HK.utf8            | zh_HK.utf8  
 UTF8       | zh_HK.utf8            | zh_HK.utf8            | zh_HK.utf8  
 EUC_CN     | zh_SG                 | zh_SG                 | zh_SG  
 UTF8       | zh_SG                 | zh_SG.utf8            | zh_SG.utf8  
 EUC_CN     | zh_SG.gb2312          | zh_SG.gb2312          | zh_SG.gb2312  
 UTF8       | zh_SG.utf8            | zh_SG.utf8            | zh_SG.utf8  
 EUC_TW     | zh_TW                 | zh_TW.euctw           | zh_TW.euctw  
 UTF8       | zh_TW                 | zh_TW.utf8            | zh_TW.utf8  
 EUC_TW     | zh_TW.euctw           | zh_TW.euctw           | zh_TW.euctw  
 UTF8       | zh_TW.utf8            | zh_TW.utf8            | zh_TW.utf8  
 UTF8       | zu_ZA                 | zu_ZA.utf8            | zu_ZA.utf8  
 LATIN1     | zu_ZA                 | zu_ZA                 | zu_ZA  
 LATIN1     | zu_ZA.iso88591        | zu_ZA.iso88591        | zu_ZA.iso88591  
 UTF8       | zu_ZA.utf8            | zu_ZA.utf8            | zu_ZA.utf8  
(869 rows)  

五、創建數據庫時指定LC_COLLATE, LC_CTYPE

例子

創建一個數據庫，lc_collate, lc_ctype分別為zh_CN.utf8

test=> create database test05 with encoding 'UTF-8' template template0 lc_collate='zh_CN.utf8' lc_ctype='zh_CN.utf8';  
CREATE DATABASE  

注意

如果指定的lc_collate, lc_ctype與模板庫的collate,ctype不兼容，會報錯。

test=> create database test04 with encoding 'UTF-8' lc_collate='zh_CN.utf8' lc_ctype='zh_CN.utf8';  
ERROR:  new collation (zh_CN.utf8) is incompatible with the collation of the template database (zh_CN.UTF-8)  
HINT:  Use the same collation as in the template database, or use template0 as template.  

解決辦法1，使用兼容的collate和ctype。

test=> create database test04 with encoding 'UTF-8' lc_collate='zh_CN.UTF-8' lc_ctype='zh_CN.UTF-8';  
CREATE DATABASE  

解決辦法2，使用template0作為模板庫。

test=> create database test05 with encoding 'UTF-8' template template0 lc_collate='zh_CN.utf8' lc_ctype='zh_CN.utf8';  
CREATE DATABASE  

六、如何修改已有數據庫的collate,ctype

目前無法直接通過alter database的語法進行修改，用戶可以使用創建新的數據庫，導出，再導入的方式。

例子

1. 創建新數據庫，指定目標collate和ctype

2. 使用pg_dump或其他客戶端工具邏輯導出源數據庫的數據

3. 使用pg_restore或其他客戶端工具，將第二步導出數據導入新數據庫

參考

https://www.postgresql.org/docs/9.6/static/sql-createdatabase.html

最後更新：2017-04-24 21:32:59

  上一篇： 如何在PostgreSQL中調試plpgsql存儲過程(pldebugger, pldbgapi)

  下一篇： 人工智能醫療領域最受期待的公司有哪些？