postgresql - 在具有违规行的多列上添加唯一约束

Question

我们正在尝试以删除重复项而不是抛出错误的方式向我们的 postgres 表添加唯一约束。唯一约束跨越两列，没有主键。例如:

i_id | term   | date_created
1    | 'mako' | 123456789
1    | 'mako' | 123451234
1    | 'tele' | 213456852
2    | 'rake' | 598521542

因此，在此示例中，我们需要先删除第二行，然后才能安全地添加唯一约束。通常我们会执行一个带有 select distinct 的删除命令，但我们没有任何行的区分键。具体来说，唯一键将位于 [i_id, term] 列之上。

(卧槽，我们从一开始就没有唯一约束吗？算了吧。)

我认为删除语句是最好的，但我不能简单地写

delete from table where row_id not in (select row_id ... distinct something ... )

因为该行没有主键。如果可能的话，我宁愿避免使用临时表。有什么建议吗？

编辑:抱歉。我们使用的是 postgres 8.4。

编辑 2:我们使用的解决方案是:

delete from table where ctid not in (
  select 
    distinct on (i_id, term)
    ctid
    from table
    order by i_id, term
);

谢谢大家!

Answer 1

DELETE FROM the_table
WHERE ctid NOT IN (SELECT min(b.ctid)
                   FROM the_table b
                   GROUP BY b.i_id, b.term)