PostgreSQL 15：创建多语言不区分大小写数据库及修改方案咨询

问题1：能否通过Collations修改已创建数据库的大小写不敏感设置且不丢失数据？ #

不能直接修改已存在数据库的默认collation，但可以通过以下方式实现大小写不敏感，且完全不会丢失数据：

• 创建自定义的大小写不敏感collation

  CREATE COLLATION ci_collation (
    PROVIDER = icu,
    LOCALE = 'und-u-ks-level2', -- 通用不区分大小写规则，也可指定具体语言如'en-US-u-ks-level2'
    DETERMINISTIC = FALSE
  );
  

• 修改现有表中需要大小写不敏感的列的collation

  ALTER TABLE your_table ALTER COLUMN your_column TYPE text COLLATE ci_collation;
  

  注：如果列是varchar等其他文本类型，将text替换为对应类型即可。
• 若要让后续新创建的表默认使用该collation，可修改数据库的默认collation（此设置仅影响新对象，现有对象需手动调整）

  ALTER DATABASE your_db SET default_collation = 'ci_collation';
  

补充：如果希望全库所有对象统一切换到目标collation，更稳妥的方式是用pg_dump导出全库数据，创建新的指定目标collation的数据库，再导入数据，这种方式能彻底覆盖所有对象，避免遗漏个别列。

问题2：如何通过Collations创建支持阿拉伯语、英语、法语三种语言的不区分大小写数据库？ #

PostgreSQL 15支持ICU collations，可通过两种方案实现需求：

方案1：创建通用多语言不区分大小写collation #

利用ICU的通用规则，同时支持多语言排序和大小写不敏感：

-- 创建多语言大小写不敏感collation
CREATE COLLATION multi_lang_ci (
  PROVIDER = icu,
  LOCALE = 'und-u-ks-level2-kb-true', -- und代表通用语言，ks-level2表示不区分大小写，kb-true适配多语言排序
  DETERMINISTIC = FALSE
);

-- 创建数据库时指定该collation
CREATE DATABASE multi_lang_db WITH 
  OWNER = your_user
  ENCODING = 'UTF8'
  LC_COLLATE = 'multi_lang_ci'
  LC_CTYPE = 'multi_lang_ci';

方案2：针对每种语言创建单独的collation #

如果需要针对不同语言使用更精准的排序规则，可以分别创建三种语言的大小写不敏感collation，后续在列级别按需指定：

-- 英语大小写不敏感collation
CREATE COLLATION en_ci (
  PROVIDER = icu,
  LOCALE = 'en-US-u-ks-level2',
  DETERMINISTIC = FALSE
);

-- 法语大小写不敏感collation
CREATE COLLATION fr_ci (
  PROVIDER = icu,
  LOCALE = 'fr-FR-u-ks-level2',
  DETERMINISTIC = FALSE
);

-- 阿拉伯语大小写不敏感collation
CREATE COLLATION ar_ci (
  PROVIDER = icu,
  LOCALE = 'ar-SA-u-ks-level2',
  DETERMINISTIC = FALSE
);

-- 创建数据库时指定其中一个为默认，后续在列上按需切换其他collation
CREATE DATABASE multi_lang_db WITH 
  OWNER = your_user
  ENCODING = 'UTF8'
  LC_COLLATE = 'en_ci'
  LC_CTYPE = 'en_ci';

注：DETERMINISTIC = FALSE是实现大小写不敏感匹配的核心配置，非确定性collation会忽略字符的大小写差异。

内容的提问来源于stack exchange，提问作者M.Bouabdallah