We have a uniqueness constraint that is supposed to prevent duplicate database rows in the licensepooldeliveries table. Unfortunately, Postgresql only enforces uniqueness constraints on fields that are not null – that is, if two identical database rows have a null value in one of the fields subject to constraint, it considers the constraint met. This is a big problem because in almost all cases, one of these fields – resource_id – is null.

We can fix this by adding a conditional unique index on the rows that have resource_id set to null, enforcing uniqueness on the remaining fields.

It's possible that this is a problem in other tables – in particular, the deliverymechanisms table has a uniqueness constraint on a field (drm_scheme) which is frequently null.