如何创建或修改PostgreSQL数据库为大小写不敏感且重音敏感？

针对你从SQL Server迁移到PostgreSQL 16遇到的大小写敏感问题，以下是具体解决方案：

1. 创建具备「大小写不敏感-重音敏感」特性的数据库 #

PostgreSQL通过**自定义ICU排序规则（Collation）**实现该需求，ICU是PostgreSQL 10+默认支持的排序规则提供商（多数预编译安装包已启用，若自行编译需确保添加--with-icu参数）。

步骤1：创建自定义排序规则 #

执行SQL语句创建符合要求的collation：

CREATE COLLATION case_insensitive_accent_sensitive (
    PROVIDER = icu,
    LOCALE = 'und-u-kf-upper-ks-level1',
    DETERMINISTIC = FALSE
);

• und：通用语言环境，适配多语言场景
• kf-upper：启用大小写不敏感比较（统一转为大写后对比）
• ks-level1：仅区分重音差异（Level1对应重音层级，跳过大小写区分逻辑）
• DETERMINISTIC = FALSE：允许非确定性匹配（支持大小写不敏感的等值判断）

步骤2：创建使用该排序规则的数据库 #

创建数据库时指定默认collation，确保所有新建表的字符列自动继承该规则：

CREATE DATABASE your_db_name
    WITH OWNER = your_user
    ENCODING = 'UTF8'
    LC_COLLATE = 'case_insensitive_accent_sensitive'
    LC_CTYPE = 'case_insensitive_accent_sensitive';

备选方案：使用citext类型 #

若仅需针对个别列实现该特性，可启用citext扩展类型：

CREATE EXTENSION IF NOT EXISTS citext;

创建列时使用citext类型，它会自动忽略大小写但保留重音敏感：

CREATE TABLE users (
    lastname citext NOT NULL
);

该方案适合无需全局修改的场景，但citext仅支持文本类型，灵活性不如自定义collation。

2. 修改现有数据库实现该特性 #

PostgreSQL无法直接修改现有数据库的LC_COLLATE和LC_CTYPE参数（这两个参数在数据库创建时即固定，涉及底层数据排序和索引结构），但可通过以下两种方式间接实现：

方案1：全库迁移（推荐） #

这是最彻底的方法，避免遗留问题：

1. 使用pg_dump导出现有数据库的数据：

pg_dump -U your_user -F c -b -v -f your_db_dump.dmp your_existing_db

2. 按照问题1的方法创建新的带目标collation的数据库
3. 使用pg_restore将数据导入新数据库：

pg_restore -U your_user -d your_new_db -v your_db_dump.dmp

4. 验证数据和查询，确认大小写不敏感特性生效

方案2：逐列修改排序规则（适合小范围调整） #

若仅需修改部分表的列，可逐个调整列的collation，并重建相关索引：

1. 修改列的collation：

ALTER TABLE your_table ALTER COLUMN lastname TYPE text COLLATE case_insensitive_accent_sensitive;

2. 重建依赖该列的索引（否则索引会失效）：

REINDEX INDEX idx_lastname;

注意：该方法需遍历所有需要修改的列和索引，操作繁琐，且无法保证全局一致性，仅适合局部调整场景。

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