PostgreSQL CASE表达式用正则做邮编约束致合法加拿大邮编插入失败

你的合法加拿大邮编被约束拦截的原因可以拆成两层：

• 正则表达式因转义问题完全失效
• CASE表达式的写法健壮性不足

第一层：正则匹配规则失效 #

PostgreSQL 的字符串转义规则存在场景差异：
当数据库参数standard_conforming_strings = off时，单引号包裹的字符串中，反斜杠会被当作转义符处理。你写的正则串'\A^[A-Z]\d[A-Z]\s\d[A-Z]\d\Z'经过转义解析后，所有带反斜杠的正则元字符的反斜杠都会被剥离，最终传给正则引擎的匹配规则变成了A^[A-Z]d[A-Z]sd[A-Z]dZ。

这个规则完全无法匹配G6V 4C9这类真实邮编，导致约束判断时code !~ 正则永远为真，CASE触发返回FALSE，直接拦截了格式完全正确的输入。

另外你写的锚点存在冗余：\A和^都是字符串开头匹配，\Z和$都是字符串结尾匹配，重复写不会提升匹配准确性，反而会增加转义出错的概率。

第二层：CASE写法的逻辑漏洞 #

你当前的CASE表达式没有写ELSE兜底分支：
PostgreSQL的CHECK约束规则为：仅当约束表达式返回严格的FALSE时才会拦截写入，返回TRUE或NULL时会放行。也就是说哪怕正则完全正确，当匹配到合法邮编、非加拿大地区的行时，CASE因为没有匹配到WHEN分支，会默认返回NULL。这种写法虽然在当前版本的PostgreSQL下不会拦截合法数据，但健壮性极差，一旦数据库参数调整、版本升级，很容易触发非预期的违规拦截。

方案1：简化正则并补全CASE兜底逻辑 #

去掉冗余的转义锚点，用通用的^/$匹配字符串边界，同时补全ELSE分支避免NULL返回值：

ALTER TABLE geolocation_postcodes
ADD CONSTRAINT canadian_format CHECK (
  CASE 
    WHEN country_code = 'CA' 
    THEN code ~ '^[A-Z]\d[A-Z]\s\d[A-Z]$'
    ELSE TRUE
  END
);

注意：在Rails迁移中写正则时，记得在Ruby字符串里把反斜杠转义，也就是把\d写成\\d，避免Ruby层把反斜杠当作普通字符处理丢失元字符。

方案2：用纯布尔逻辑实现（推荐） #

完全不需要嵌套CASE，用逻辑或的等价规则就能实现完全一致的校验效果，更简洁也没有转义和NULL值的坑：

ALTER TABLE geolocation_postcodes
ADD CONSTRAINT canadian_format CHECK (
  country_code != 'CA' 
  OR code ~ '^[A-Z]\d[A-Z]\s\d[A-Z]$'
);

逻辑等价于：

• 非加拿大地区的行，直接放行
• 加拿大地区的行，必须符合邮编格式才放行，否则拦截

补充：新增CHECK约束前，先排查表内存量数据是否符合格式要求，否则会因为存量数据违规导致约束创建失败。

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