Can't create unique index in postgres, it says "key is duplicate" even when it's not

Question

I created a unique index for a table and got the following error:

SQL error: ERROR: could not create unique index "unique_product" DETAIL: Key (mastercode)=() is duplicated

So i run a query to check for duplicated records and really found some duplicates:

select * from product p where (select count(*) from product pp where pp.mastercode = p.mastercode) > 1

But even after deleting them i could not create the index, it shows the same error and the query to check for duplicates shows nothing.

Looks like it didn't update the indices after deleting the duplicates, or something like that. How can i solve this?

UPDATE Something i forgot to mention but may be important, i already have an index on the field mastercode, which is a default index (not unique). Don't know if this has something to do.

Answer 1

Check the results from this query:

SELECT  mastercode, count(*)
FROM product
GROUP BY mastercode
HAVING count(*) > 1; -- not unique

Answer 2

I believe you have an instance of a null mastercode, and are trying to insert another null mastercode.

Try select * from product where mastercode is null;