PostgreSQL执行含substring的UPDATE语句报错：negative substring length not allowed求助

这个报错的核心原因是你在substring(filcase_type,1,position('SR' in filcase_type)-1)里，当position('SR' in filcase_type)返回0（也就是字段里找不到"SR"子串）时，计算后的长度会变成-1，而PostgreSQL不允许substring使用负长度参数。结合你的UPDATE语句逻辑，这些是你可能遗漏的排查点：

• 未过滤position('SR')返回0的记录：你的WHERE条件是right(m.filcase_type,2)='SR'，理论上这些记录的filcase_type末尾是"SR"，按说position('SR')应该能找到位置，但要注意：如果filcase_type就是"SR"本身，那position('SR')返回1，减1后是0，这时候substring(...,1,0)会返回空字符串，虽然不会报错，但可能不是你想要的结果；另外要检查是否有数据不符合right(...,2)='SR'的情况被误包含进来？比如字段值末尾的"SR"前面有特殊字符，导致position找不到匹配？

• 忽略了position()函数的返回规则：PostgreSQL的position(substr in str)如果找不到子串会返回0，而不是NULL或者其他值。你直接用position(...) -1，当返回0时就会得到-1，这直接触发了报错。所以必须先判断position()的结果是否大于0，再进行减法操作。比如可以用CASE WHEN position('SR' in filcase_type) > 0 THEN position(...) -1 ELSE 0 END来避免负数。

• 子查询没有过滤无效数据：你的子查询是从整个main表取数据，而不是只取符合right(filcase_type,2)='SR'的记录，这意味着子查询里会包含很多position('SR')=0的记录，这些记录在计算substring时会产生负数长度，虽然主查询的WHERE条件会过滤掉大部分，但子查询本身执行时就会触发报错，因为它会先计算所有行的substring。所以子查询里也应该加上WHERE right(filcase_type,2)='SR'的条件，只处理需要更新的行，避免无效计算。

• 未考虑字段值为空或长度不足的情况：如果filcase_type是NULL，或者长度小于2，right(filcase_type,2)会返回NULL或者整个字符串，这时候不会匹配WHERE条件，但如果有这种数据，子查询里的position('SR')会返回0，同样导致负数长度。所以可以在子查询里加上filcase_type IS NOT NULL AND length(filcase_type) >=2的过滤条件。

举个修正后的语句例子供你参考：

UPDATE main AS m 
SET filcase_type = subquery.filcase 
FROM (
    SELECT 
        filcase_type,
        CASE 
            WHEN position('SR' IN filcase_type) > 1 THEN substring(filcase_type,1,position('SR' IN filcase_type)-1)
            ELSE '' -- 或者根据你的业务需求设置默认值
        END AS filcase 
    FROM main 
    WHERE right(filcase_type,2)='SR' 
      AND filcase_type IS NOT NULL 
      AND length(filcase_type) >=2
) AS subquery 
WHERE right(m.filcase_type,2)='SR';

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