Postgres中筛选关联不匹配的audit_log_event并修复数据

筛选关联不匹配的audit_log_event记录 #

要找出audit_log_event中family_id与对应genus所属family不一致的记录，可通过关联genus表对比字段值实现：

SELECT ale.*
FROM audit_log_event ale
INNER JOIN genus g 
  ON ale.genus_id = g.id
WHERE ale.family_id != g.family_id;

如果业务允许genus_id为空，且这类记录也需要排查（比如family_id非空但genus_id为空是否符合规则），可根据实际需求调整WHERE条件，比如添加OR ale.genus_id IS NULL AND ale.family_id IS NOT NULL（需结合业务逻辑判断）。

直接用Postgres UPDATE修复数据 #

完全可以通过Postgres的UPDATE语句直接修复，无需导出数据用程序处理，效率更高。修复逻辑是将audit_log_event的family_id更新为对应genus关联的family_id：

安全修复步骤（建议先验证再执行） #

1. 开启事务，避免误操作无法回滚：

BEGIN;

2. 先查询待更新的记录，确认数据无误：

SELECT 
  ale.id, 
  ale.family_id AS current_family_id, 
  g.family_id AS correct_family_id
FROM audit_log_event ale
INNER JOIN genus g 
  ON ale.genus_id = g.id
WHERE ale.family_id != g.family_id;

3. 确认待更新记录符合预期后，执行更新：

UPDATE audit_log_event ale
SET family_id = g.family_id
FROM genus g
WHERE ale.genus_id = g.id
AND ale.family_id != g.family_id;

4. 提交事务完成修复：

COMMIT;

如果执行过程中发现异常，可执行ROLLBACK;撤销所有操作。

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