PostgreSQL数据库建库时UTF8与UTF-8哪个写法更标准

初始化数据库或者初始化完成后再使用create database创建数据库时，有三个参数的设置值与UTF8有关，分别是数据库服务器编码参数encoding、排序规则参数lc_collate、字符分类参数lc_ctype。

下面分别测试这三个参数：

1.测试encoding参数的值分别为utf8、utf-8、UTF8、UTF-8

可以看到，下面的语句都可以创建成功。

postgres=# CREATE DATABASE test_encoding1  WITH ENCODING 'utf8' TEMPLATE=template0; 
CREATE DATABASE
postgres=# CREATE DATABASE test_encoding2  WITH ENCODING 'UTF8' TEMPLATE=template0;
CREATE DATABASE
postgres=# CREATE DATABASE test_encoding3  WITH ENCODING 'utf-8' TEMPLATE=template0; 
CREATE DATABASE
postgres=# CREATE DATABASE test_encoding4  WITH ENCODING 'UTF-8' TEMPLATE=template0; 
CREATE DATABASE

数据库里实际存储的是"UTF8"，所以标准的encoding值应该写"UTF8"。

postgres=# select datname,pg_encoding_to_char(encoding) as encoding from pg_database where datname like 'test_encoding%';
    datname     | encoding 
----------------+----------
 test_encoding1 | UTF8
 test_encoding2 | UTF8
 test_encoding3 | UTF8
 test_encoding4 | UTF8
(4 rows)

2.测试lc_collate参数的值分别为en_US.UTF-8、en_US.UTF8、en_US.utf8、en_US.utf-8

可以看到，下面的语句使用template0作为模板库可以创建成功。

postgres=# CREATE DATABASE test_lc_collate1 WITH ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' TEMPLATE=template0; 
CREATE DATABASE
postgres=# CREATE DATABASE test_lc_collate2 WITH ENCODING 'UTF8' LC_COLLATE='en_US.UTF8' TEMPLATE=template0; 
CREATE DATABASE
postgres=# CREATE DATABASE test_lc_collate3 WITH ENCODING 'UTF8' LC_COLLATE='en_US.utf8' TEMPLATE=template0; 
CREATE DATABASE
postgres=# CREATE DATABASE test_lc_collate4 WITH ENCODING 'UTF8' LC_COLLATE='en_US.utf-8' TEMPLATE=template0; 
CREATE DATABASE

数据库里存储的是实际输入的lc_collate值

postgres=# select datname,datcollate from pg_database where datname like 'test_lc_collate%';
     datname      | datcollate  
------------------+-------------
 test_lc_collate1 | en_US.UTF-8
 test_lc_collate2 | en_US.UTF8
 test_lc_collate3 | en_US.utf8
 test_lc_collate4 | en_US.utf-8
(4 rows)

3.测试lc_ctype参数的值分别为en_US.UTF-8、en_US.UTF8、en_US.utf8、en_US.utf-8

可以看到，下面的语句使用template0作为模板库可以创建成功。

postgres=# CREATE DATABASE test_lc_ctype1 WITH ENCODING 'UTF8' LC_CTYPE='en_US.UTF-8' TEMPLATE=template0; 
CREATE DATABASE
postgres=# CREATE DATABASE test_lc_ctype2 WITH ENCODING 'UTF8' LC_CTYPE='en_US.UTF8' TEMPLATE=template0; 
CREATE DATABASE
postgres=# CREATE DATABASE test_lc_ctype3 WITH ENCODING 'UTF8' LC_CTYPE='en_US.utf8' TEMPLATE=template0; 
CREATE DATABASE
postgres=# CREATE DATABASE test_lc_ctype4 WITH ENCODING 'UTF8' LC_CTYPE='en_US.utf-8' TEMPLATE=template0; 
CREATE DATABASE

数据库里存储的也是实际输入的lc_ctype值

postgres=# select datname,datctype from pg_database where datname like 'test_lc_ctype%';
    datname     |  datctype   
----------------+-------------
 test_lc_ctype4 | en_US.utf-8
 test_lc_ctype1 | en_US.UTF-8
 test_lc_ctype2 | en_US.UTF8
 test_lc_ctype3 | en_US.utf8
(4 rows)

4.相关的错误信息

遇到如下的错误提示，说明我们创建数据库时没有使用与模板库一致的collation。

• new collation (en_US.utf8) is incompatible with the collation of the template database (en_US.UTF-8)
• new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf8)
• new collation (zh_CN.UTF-8) is incompatible with the collation of the template database (en_US.UTF-8)

5.总结

参考pg_collation系统表，推荐的写法如下：

--创建UTF8字符集数据库
create database zabbix 
WITH owner zabbix 
ENCODING 'UTF8' 
LC_COLLATE='en_US.utf8' 
LC_CTYPE='en_US.utf8'
 TEMPLATE=template0;

保持联系

从2019年12月开始写第一篇文章，分享的初心一直在坚持，本人现在组建了一个PG乐知乐享交流群，欢迎关注我文章的小伙伴进群吹牛唠嗑，交流技术，互赞文章。

如果群二维码失效可以加我微信。