Some time back, Costin Grigoras <costing@cs.pub.ro> wrote:
> how could duplicate records appear when the unique index existed ?
After re-reading this old bug report, I realized that all of the methods
you were applying would make use of the index itself to search for
duplicates --- the count(*) subquery and the trigger's delete would most
likely use indexscan plans, unless you were to take steps to prevent it.
That means that we only have to make one assumption to explain all the
symptoms: the index got corrupted in such a way that it would not find
certain rows. Then it would be possible to insert new rows with the
same keys without the duplication being detected, either by the index
itself or by the queries you were using.
When you tried to reindex, the uncorrupted new index of course detected
the duplicates.
Of course the evidence is long gone about *how* the index got
corrupted. Had you had any recent system crashes or hardware problems?
regards, tom lane