PostgreSQL中UTF-8编码异常时查看数据的方法求助

临时查看脏数据（不修改数据） #

当查询包含data列触发编码错误时，可通过字节转换绕过UTF-8校验，先查看数据内容：

• 查看data列的原始十六进制字节：

  SELECT id, encode(data::bytea, 'hex') FROM t_example WHERE id = 'hello';
  

• 将data列转换为合法UTF-8（自动替换无效字节为?）：

  SELECT id, convert_from(data::bytea, 'UTF8') FROM t_example WHERE id = 'hello';
  

修复单条脏数据 #

如果确认是目标行的data列存在无效字节，可直接更新该列：

• 替换指定无效字节序列为空（或根据实际需求替换为合法字符）：

  UPDATE t_example 
  SET data = convert_from(replace(data::bytea, '\xea\x3c\x68'::bytea, ''::bytea), 'UTF8') 
  WHERE id = 'hello';
  

• 若不需要保留错误内容，直接清空该列：

  UPDATE t_example SET data = '' WHERE id = 'hello';
  

紧急临时绕过编码校验（不推荐长期使用） #

临时修改会话的客户端编码为LATIN1，跳过UTF-8校验（返回内容可能乱码，但能查询到数据）：

SET client_encoding TO 'LATIN1';
SELECT id, data, test FROM t_example WHERE id = 'hello';
-- 用完改回原编码
SET client_encoding TO 'UTF8';

批量排查脏数据 #

如果想检查整个表是否有其他类似错误，可使用以下查询找出所有包含无效UTF-8的行：

SELECT id 
FROM t_example 
WHERE data::text IS NOT NULL 
AND NOT data ~ '^[\x00-\x7F\xC2-\xF4][\x80-\xBF]*$';

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