database - 如何删除具有外键依赖项的重复行？

我敢肯定这很普通，但是Google并没有帮助。我正在尝试在PostgreSQL 9.1中编写一个简单的存储过程，该存储过程将从父cpt表中删除重复的条目。父表cpt由定义为以下内容的子表lab引用:CREATE TABLE lab ( recid serial NOT NULL, cpt_recid integer, ........ CONSTRAINT cs_cpt FOREIGN KEY (cpt_recid) REFERENCES cpt (recid) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT, ...);我遇到的最大问题是如何获取失败的记录，以便可以在EXCEPTION子句中使用它，以将子行从lab移至一个可接受的键，然后循环遍历并从cpt表中删除不必要的记录。这是(非常错误的)代码:CREATE OR REPLACE FUNCTION h_RemoveDuplicateCPT() RETURNS void AS$BODY$BEGINLOOP BEGIN DELETE FROM cpt WHERE recid IN ( SELECT recid FROM ( SELECT recid, row_number() over (partition BY cdesc ORDER BY recid) AS rnum FROM cpt) t WHERE t.rnum > 1) RETURNING recid; IF count = 0 THEN RETURN; END IF; EXCEPTION WHEN foreign_key_violation THEN RAISE NOTICE 'fixing unique_violation'; RAISE NOTICE 'recid is %' , recid; END;END LOOP;END;$BODY$LANGUAGE plpgsql VOLATILE; 最佳答案 您可以使用带有data-modifying CTEs的单个sqlt_strong SQL语句来更有效地执行此操作。WITH plan AS ( SELECT * FROM ( SELECT recid, min(recid) OVER (PARTITION BY cdesc) AS master_recid FROM cpt ) sub WHERE recid <> master_recid -- ... <> self ) , upd_lab AS ( UPDATE lab l SET cpt_recid = p.master_recid -- link to master recid ... FROM plan p WHERE l.cpt_recid = p.recid )DELETE FROM cpt cUSING plan pWHERE c.recid = p.recidRETURNING c.recid;db fiddle here(第11页) SQL Fiddle(第9.6页)这应该是更快，更干净的。循环是相对昂贵的，异常处理则相对更昂贵。更重要的是，lab中的引用会自动重定向到cpt中的相应主行，而这不在您的原始代码中。因此，您可以一次删除所有重复项。如果愿意，您仍然可以将其包装在plpgsql或SQL函数中。解释在第一个CTE plan中，使用相同的cdesc标识每个分区中的主行。在您的情况下，行的最小recid。 在第二个CTE upd_lab中，将引用dupe的所有行重定向到cpt中的主行。 最后，删除重复对象，这不会引起异常，因为相关行实际上同时链接到其余的主行。 ON DELETE RESTRICT所有CTE和语句的主查询都对基础表的相同快照进行操作，实际上是并发。他们看不到彼此对基础表的影响: Delete parent if it's not referenced by any other child 有人可能希望ON DELETE RESTRICT的FK约束引发异常，因为，[根据文档] [3]: Referential actions other than the NO ACTION check cannot be deferred, even if the constraint is declared deferrable.但是，以上语句是一个单个命令，[再次是手册] [3]: A constraint that is not deferrable will be checked immediately after every command.大胆强调我的。当然，也适用于限制较少的默认ON DELETE NO ACTION。但是请注意不要将并发事务写入相同的表，但这是一个普遍考虑，而不是特定于此任务。异常(exception)适用于UNIQUE和PRIMARY KEY约束，但是与这种情况无关: Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?关于database - 如何删除具有外键依赖项的重复行？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30958622/