如何为PostgreSQL 16+设置默认自定义ICU排序规则？

要实现「大小写不敏感、重音不敏感，但特殊字符敏感」的查询规则，并将其设为数据库默认（无需每次查询显式指定），可按以下步骤操作：

1. 创建自定义排序规则（Collation） #

PostgreSQL 16支持基于ICU（Unicode国际化组件）的自定义排序规则，通过设置排序强度即可匹配需求：

• 排序强度设为primary：仅区分基础字符（如a和b），自动忽略大小写、重音及变音符号
• 特殊字符区分：ICU默认规则会保留-、_这类符号的差异，无需额外配置

执行以下SQL创建自定义规则：

CREATE COLLATION ci_ai (
  PROVIDER = icu,
  LOCALE = 'und@colStrength=primary',
  DETERMINISTIC = FALSE
);

• und是通用Locale，适配多语言场景；若业务仅针对特定语言，可替换为对应Locale（如en-US）
• DETERMINISTIC = FALSE是实现大小写/重音不敏感匹配的必要设置，代表非确定性排序规则

2. 设置数据库默认排序规则 #

方式一：新建数据库时指定 #

创建数据库时直接绑定自定义规则，注意必须使用template0作为模板（避免继承原有模板的排序规则）：

CREATE DATABASE your_app_db
WITH OWNER = your_db_user
ENCODING = 'UTF8'
LC_COLLATE = 'ci_ai'
LC_CTYPE = 'ci_ai'
TEMPLATE = template0;

方式二：修改已有数据库 #

若要调整已存在的数据库，执行以下SQL后需重启PostgreSQL服务：

ALTER DATABASE your_app_db SET LC_COLLATE = 'ci_ai';
ALTER DATABASE your_app_db SET LC_CTYPE = 'ci_ai';

注意：修改已有数据库的默认规则后，现有表的列不会自动继承新规则，需手动修改列的排序规则或重新创建表。

3. 验证效果 #

执行测试查询确认是否符合需求：

SELECT
  'a' = 'A' AS test1,
  'a' = 'á' AS test2,
  'a' = 'b' AS test3,
  'a-b' = 'a_b' AS test4;

预期返回结果：test1=true, test2=true, test3=false, test4=false

注意事项 #

• 非确定性规则的索引：PostgreSQL 12+支持基于非确定性排序规则创建索引，若数据库默认使用ci_ai，直接创建索引即可：

  CREATE INDEX idx_users_email ON users (email);
  

• 检查ICU支持：若创建排序规则时报错，说明你的PostgreSQL未编译ICU支持。可通过SELECT name FROM pg_collation WHERE provider = 'icu';查看是否存在ICU类型规则，若为空则需更换包含ICU支持的PostgreSQL发行版。

内容的提问来源于stack exchange，提问作者Leonardo