安装 PostgreSQL，编码和区域设置知多少？
安装 PostgreSQL 数据库时，有很多非常关键的设置。而这些设置在安装后，都不太容易再修改调整了。
区域设置和编码
在对字符串进行排序时，一切都与区域设置以及 PostgreSQL 如何使用它们有关。PostgreSQL 使用了底层操作系统提供的区域设置，默认情况下为 “C” 或 “POSIX”。要了解其工作原理，让我们先做一些简单的演示，然后再一步步深入。
这里使用的系统是 Debian 12：
$ cat /etc/debian_version
12.2在系统上已经修改了当前环境，使其不设置任何特定于区域设置的环境变量：
$ env | grep LC当我们从这个环境初始化一个新的 PostgreSQL 实例时，我们在新实例中会获得什么区域设置和编码设置？
$ initdb --pgdata=/var/tmp/xx
$ export PGPORT=8888
$ pg_ctl --pgdata=/var/tmp/xx --log=/dev/null start
$ psql -p 8888 -c "\l"
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+-----------+-----------------+---------+-------+------------+-----------+-----------------------
postgres | postgres | SQL_ASCII | libc | C | C | | |
template0 | postgres | SQL_ASCII | libc | C | C | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | SQL_ASCII | libc | C | C | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(3 rows)由于环境中没有定义任何特定设置，并且我们也没有使用 initdb 指定任何设置，因此，我们的区域设置为了 “C”，编码设置为了 “SQL_ASCII”。尤其是 “SQL_ASCII” 的编码设置不是一个好的选择，因为上面链接的文档已经明确说明了这一点：
来自 PostgreSQL 文档：
设置为
SQL_ASCII的行为与其他设置大不相同。当服务器字符集为SQL_ASCII时，服务器会根据 ASCII 标准来解释字节值 0–127，而字节值 128–255 则被视为未解释的字符。当设置为SQL_ASCII时，不会进行编码转换。因此，这个设置与其说是在声明使用一个特定编码，不如说是一种对编码无知的声明。在大多数情况下，如果您正在处理任何非 ASCII 数据，则使用SQL_ASCII设置都是不明智的，因为 PostgreSQL 将会无法转换或验证非 ASCII 字符。
更改默认编码
换句话说，您应该避免这种情况。那么，如何才能更改默认编码？有两种方法可以做到这一点。第一个方法是，在您的环境中正确设置好区域设置：
$ export LANG=en_US.UTF-8
$ initdb --pgdata=/var/tmp/yy
$ export PGPORT=8889
$ pg_ctl --pgdata=/var/tmp/yy --log=/dev/null start
$ psql -p 8889 -c "\l"
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
template0 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(3 rows)这将为您提供 UTF8 的编码和 en_US 的区域设置。另一个方法是指示 initdb 你想要什么，这样也可以给你完全相同的东西：
$ initdb --pgdata=/var/tmp/zz --locale="en_US.UTF8"
$ export PGPORT=8890
$ pg_ctl --pgdata=/var/tmp/zz --log=/dev/null start
$ psql -p 8890 -c "\l"
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
postgres | postgres | UTF8 | libc | en_US.UTF8 | en_US.UTF8 | | |
template0 | postgres | UTF8 | libc | en_US.UTF8 | en_US.UTF8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.UTF8 | en_US.UTF8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(3 rows)使用本地区域设置
如果你想使用你本地的区域设置，你也可以这样做：
$ initdb --pgdata=/var/tmp/oo --locale="zh_CN.UTF8"
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
initdb: error: invalid locale name "zh_CN.UTF8"
initdb: hint: If the locale name is specific to ICU, use --icu-locale.这里的问题是什么？答案很简单：操作系统上不存在该区域设置：
$ locale -a
C
C.utf8
en_US.utf8
POSIX在 Debian 上，这需要通过在 /etc/locale.gen 中启用该区域设置，然后运行 “locale-gen”，才能生成：
$ sudo vi /etc/locale.gen
$ grep zh_CN /etc/locale.gen
# zh_CN ISO-8859-1
zh_CN.UTF-8 UTF-8
$ sudo locale-gen
Generating locales (this might take a while)...
zh_CN.UTF-8... done
en_US.UTF-8... done
Generation complete.
$ locale -a
C
C.utf8
zh_CN.utf8
en_US.utf8
POSIX现在，新的区域设置已经存在，也可以将其提供给 initdb（或在环境中指定）：
$ initdb --pgdata=/var/tmp/oo --locale="zh_CN.UTF8"
$ export PGPORT=8891
$ pg_ctl --pgdata=/var/tmp/oo --log=/dev/null start
$ psql -p 8891 -c "\l"
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
postgres | postgres | UTF8 | libc | zh_CN.UTF8 | zh_CN.UTF8 | | |
template0 | postgres | UTF8 | libc | zh_CN.UTF8 | zh_CN.UTF8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | zh_CN.UTF8 | zh_CN.UTF8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(3 rows)使用 ICU 区域设置
如果您不想使用由 glibc 提供的区域设置，也可以使用 ICU（International Components for Unicode）。当您在编译 PostgreSQL 版本时带有 ICU 支持，并且它已安装在您的系统上，那么这是您的另一个选择。在 Debian 12 上是这样的：
$ apt info libicu72
Package: libicu72
Version: 72.1-3
Priority: optional
Section: libs
Source: icu
Maintainer: Laszlo Boszormenyi (GCS) gcs@debian.org
Installed-Size: 37.0 MB
Depends: libc6 (>= 2.34), libgcc-s1 (>= 3.0), libstdc++6 (>= 12)
Breaks: libiculx63 (<< 63.1-5), openttd (<< 1.8.0-2~)
Replaces: libiculx63 (<< 63.1-5)
Homepage: https://icu.unicode.org/
Tag: role::shared-lib
Download-Size: 9,376 kB
APT-Manual-Installed: no
APT-Sources: http://deb.debian.org/debian bookworm/main amd64 Packages
Description: International Components for Unicode
ICU is a C++ and C library that provides robust and full-featured
Unicode and locale support. This package contains the runtime
libraries for ICU.要使用 ICU，您需要在初始化新实例时，切换区域设置的提供方：
$ initdb --pgdata=/var/tmp/qq --locale-provider=icu --icu-locale=en-US-x-icu
$ export PGPORT=8892
$ pg_ctl --pgdata=/var/tmp/qq --log=/dev/null start
$ psql -p 8892 -c "\l"
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+-------------+-------------+-------------+-----------+-----------------------
postgres | postgres | UTF8 | icu | en_US.UTF-8 | en_US.UTF-8 | en-US-x-icu | |
template0 | postgres | UTF8 | icu | en_US.UTF-8 | en_US.UTF-8 | en-US-x-icu | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | icu | en_US.UTF-8 | en_US.UTF-8 | en-US-x-icu | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(3 rows)在实例运行起来后，您就可以询问 PostgreSQL 有哪些可用的排序规则：
select * from pg_collation;
oid | collname | collnamespace | collowner | collprovider | collisdeterministic | collencoding | collcollate | collctype | colliculocale | collicurules | collversion
-------+------------------------+---------------+-----------+--------------+---------------------+--------------+-------------+------------+------------------+--------------+-------------
100 | default | 11 | 10 | d | t | -1 | | | | |
950 | C | 11 | 10 | c | t | -1 | C | C | | |
951 | POSIX | 11 | 10 | c | t | -1 | POSIX | POSIX | | |
962 | ucs_basic | 11 | 10 | c | t | 6 | C | C | | |
963 | unicode | 11 | 10 | i | t | -1 | | | und | | 153.120
12348 | C.utf8 | 11 | 10 | c | t | 6 | C.utf8 | C.utf8 | | |
12349 | zh_CN.utf8 | 11 | 10 | c | t | 6 | zh_CN.utf8 | zh_CN.utf8 | | | 2.36
12350 | en_US.utf8 | 11 | 10 | c | t | 6 | en_US.utf8 | en_US.utf8 | | | 2.36
12351 | zh_CN | 11 | 10 | c | t | 6 | zh_CN.utf8 | zh_CN.utf8 | | | 2.36
12352 | en_US | 11 | 10 | c | t | 6 | en_US.utf8 | en_US.utf8 | | | 2.36
12353 | und-x-icu | 11 | 10 | i | t | -1 | | | und | | 153.120
12354 | af-x-icu | 11 | 10 | i | t | -1 | | | af | | 153.120.42
12355 | af-NA-x-icu | 11 | 10 | i | t | -1 | | | af-NA | | 153.120.42
12356 | af-ZA-x-icu | 11 | 10 | i
THE END
