PostgreSQL字符类型分区表FROM TO取值及大小写不敏感设置问题

原因 #

1. PostgreSQL范围分区的FROM ... TO ...语法中，TO后的值为排他边界，你原SQL中写的TO ('^z')只会收录字典序小于^z的字符串，z开头的字符串字典序大于等于^z，自然无法进入该分区。
2. 你原SQL中^前缀是错误用法：范围分区的字符串匹配基于逐字符字典序比较，不会识别正则语法，加^后只会匹配以^g开头的特殊字符串，正常g开头的姓名本身就匹配不到。

修复SQL #

把边界调整为FROM ('g') TO ('{')即可：ASCII码中{字符刚好排在小写字母z的下一位，所有g/z开头的小写字符串都满足>= 'g' 且 < '{'的匹配规则：

CREATE TABLE agents_gz
PARTITION OF agents
FOR VALUES FROM ('g') TO ('{');

有两种常用实现方案，不需要修改业务层的读写逻辑：

• 方案1：使用citext类型作为分区键
  1. 先启用citext扩展，该类型自带大小写不敏感的比较规则：

  CREATE EXTENSION IF NOT EXISTS citext;
  

  2. 将父表的name字段类型修改为citext：

  ALTER TABLE agents ALTER COLUMN name TYPE citext;
  

  3. 直接使用上面修复后的分区创建语句即可，不管是G、g开头还是Z、z开头的姓名都会自动匹配到该分区。
• 方案2：基于小写转换表达式分区
  如果不想修改原有字段类型，可以将父表的分区键改为LOWER(name)：
  1. 调整父表的分区规则（需要先删除原有错误分区和分区规则）：

  CREATE TABLE agents (
    -- 保留原有所有字段定义，仅修改分区规则
  ) PARTITION BY RANGE (LOWER(name));
  

  2. 分区创建语句和上面修复后的写法完全一致，数据库会自动计算LOWER(name)的值匹配分区，自动兼容大小写不同的姓名。

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