自建PostgreSQL迁移至AWS RDS后唯一键插入异常排查求助

1. 排查大小写与隐藏空白字符 #

PostgreSQL默认区分大小写，且可能存在看不见的空白字符（空格、制表符、换行符等）导致匹配失败。执行以下SQL验证：

SELECT email, octet_length(email) AS char_length, encode(email, 'hex') AS hex_code
FROM patient
WHERE lower(trim(email)) = lower(trim('***')); -- 将***替换为报错的邮箱内容

• 对比返回的char_length和你插入的邮箱长度，确认是否有差异
• 通过hex_code查看邮箱的十六进制编码，排查是否存在隐藏字符

2. 检查未提交事务与锁冲突 #

如果存在未提交的插入事务，会导致查询看不到数据但插入触发约束。执行以下SQL查看活跃事务：

SELECT pid, query, state, xact_start
FROM pg_stat_activity
WHERE datname = '你的数据库名'
AND query LIKE '%patient%';

• 找到未提交的异常事务，可等待其提交/回滚，或用pg_terminate_backend(pid)终止进程

3. 验证表数据与索引的一致性 #

重建索引后仍有问题，直接检查表内是否存在隐藏重复数据：

-- 查找表内重复的邮箱记录
SELECT email, COUNT(*) AS count
FROM patient
GROUP BY email
HAVING COUNT(*) > 1;

-- 查看唯一索引的有效性
SELECT indrelid::regclass, indexrelid::regclass, indisvalid
FROM pg_index
WHERE indexrelid = 'ix_patient_email'::regclass;

若indisvalid返回f，说明索引无效；若查到重复数据，说明迁移过程中存在数据不一致

4. 确认查询的是RDS主库 #

若你的RDS配置了只读副本，需确认查询操作是否连接到主库。副本同步延迟可能导致查询看不到最新数据，但插入主库时触发约束，直接连接主库执行查询验证即可。

5. 删除并重建唯一约束 #

有时单纯重建索引无法解决问题，直接删除约束后重新创建：

-- 删除原有唯一约束
ALTER TABLE patient DROP CONSTRAINT ix_patient_email;

-- 重新创建唯一约束
ALTER TABLE patient ADD CONSTRAINT ix_patient_email UNIQUE (email);

创建前务必执行全表去重查询，确保表内无重复数据。

6. 检查数据库字符集一致性 #

迁移时字符集不匹配可能导致邮箱存储值与显示值不一致，执行以下命令查看字符集：

SHOW server_encoding;
SHOW client_encoding;
SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = '你的数据库名';

确保自建库与RDS的字符集一致（建议统一为UTF8）。

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