PostgreSQL日文字符编码异常求助：存储乱码与约束冲突问题

问题根源 #

数据库服务端编码、客户端连接编码、应用字符集不统一，导致日文字符被转成错误字节（显示为x1a或方块），重复插入时乱码内容一致触发唯一约束冲突。

解决步骤 #

1. 检查数据库编码配置 #

执行以下SQL查看编码设置：

SELECT name, setting FROM pg_settings WHERE name LIKE '%encoding%';

确保：

• server_encoding为UTF8（PostgreSQL的UTF8是完整支持多字节字符的编码，禁止使用SQL_ASCII）
• client_encoding需与应用、终端的字符集一致（推荐设为UTF8）

2. 确认字段字符集配置 #

检查sLabelJA字段的字符集与排序规则：

SELECT column_name, data_type, collation_name 
FROM information_schema.columns 
WHERE table_name = 'NmsCountry' AND column_name = 'sLabelJA';

要求字段类型为VARCHAR或TEXT，且collation_name为ja_JP.UTF8（或数据库默认UTF8排序规则），若为SQL_ASCII需修改字段：

ALTER TABLE NmsCountry ALTER COLUMN sLabelJA TYPE VARCHAR(255) COLLATE "ja_JP.UTF8";

3. 修正客户端连接编码 #

• psql终端：连接后执行SET client_encoding = 'UTF8';，或启动psql时加参数psql -d your_db -U your_user -c "SET client_encoding='UTF8'"
• 应用连接：在数据库连接字符串中添加编码参数，如JDBC连接串追加?clientEncoding=UTF8，Python的psycopg2连接时指定client_encoding='UTF8'

4. 清理乱码数据并重新插入 #

先删除已插入的乱码记录：

DELETE FROM NmsCountry WHERE sLabelJA = '¿¿¿¿¿' OR sLabelJA ~ '[^[:print:]]';

确保应用端输出的日文字符为UTF8编码后，重新执行插入操作。

5. 验证修复效果 #

• 在pgAdmin中刷新表，查看sLabelJA字段是否正常显示日文
• 在终端psql中执行SET client_encoding='UTF8';后查询表，确认日文无乱码

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