PostgreSQL中将camelCase转为snake_case时出现编码异常问题

正确SQL语句（两种可选） #

方法1：用美元引号避免转义 #

UPDATE my_table
SET code = LOWER(REGEXP_REPLACE(SPLIT_PART(type, '::', 2), '([a-z])([A-Z])', $$\1_\2$$, 'g'));

方法2：用$n引用捕获组 #

UPDATE my_table
SET code = LOWER(REGEXP_REPLACE(SPLIT_PART(type, '::', 2), '([a-z])([A-Z])', '$1_$2', 'g'));

异常原因 #

你看到的\x01、\x02是ASCII控制字符，问题出在PostgreSQL对字符串常量的转义规则上：
当你直接写'\1'时，PostgreSQL会把它解析为ASCII码为1的特殊字符（而非正则捕获组引用），'\2'对应ASCII码为2的字符，最终导致替换结果出现异常编码。

测试验证 #

执行以下查询可以快速验证修复效果：

SELECT LOWER(REGEXP_REPLACE('FooBar', '([a-z])([A-Z])', '$1_$2', 'g'));

返回结果为foo_bar，符合预期格式。

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