PostgreSQL唯一邮箱列的推荐处理方式：排序规则、lower索引或citext？

一、优先选非确定性排序规则 #

PostgreSQL 官方目前更推荐用非确定性排序规则替代 citext，能让数据库自动忽略邮箱的大小写差异，直接实现唯一约束，不用额外做大小写转换。

具体操作：

• 创建适配的排序规则：如果没有现成的不区分大小写排序规则，可以自己创建一个通用的版本：

  CREATE COLLATION case_insensitive (
    PROVIDER = icu,
    LOCALE = 'und-u-ks-level2',
    DETERMINISTIC = FALSE
  );
  

  这个规则基于 Unicode 通用区域，二级排序会忽略大小写，适合邮箱这类场景。
• 给邮箱列绑定排序规则并加唯一约束：
  创建表时直接指定：

  CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) COLLATE case_insensitive UNIQUE NOT NULL
  );
  

  这样插入 test@Example.COM 和 TEST@example.com 会触发唯一约束冲突，正好符合邮箱唯一性的要求。

这种方式的好处是不用手动处理大小写，查询时直接用原始邮箱值就能匹配，索引和约束逻辑更简洁。

二、传统方案：lower() 函数索引 #

如果你的 PostgreSQL 版本较低（低于12，不支持自定义非确定性排序规则），可以继续用这种传统方式：

• 创建函数唯一索引：

  CREATE UNIQUE INDEX idx_users_lower_email ON users (lower(email));
  

• 可选：用生成列强化约束：
  要是想让约束更直观，能在表结构里看到，可以加一个生成列：

  ALTER TABLE users ADD COLUMN email_lower VARCHAR(255) GENERATED ALWAYS AS (lower(email)) STORED;
  ALTER TABLE users ADD CONSTRAINT unique_email_lower UNIQUE (email_lower);
  

  这样不同大小写的邮箱会因为生成列的值重复而被拦截。

这个方案兼容性拉满，所有版本都支持，但缺点是查询时得手动用 lower(email) 来匹配，不然可能走不到索引。

三、不建议再用 citext #

虽然 citext 以前是常用方案，但现在官方明确建议用排序规则替代它。citext 本质是自动对文本做大小写转换，在特殊字符处理、排序规则兼容这些场景下，灵活性不如原生排序规则，而且未来的支持力度可能会降低。

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