CODEWITHSUNDEEP
postgresql
Mateus Viccari
Mateus Viccari

Reputation: 7709

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

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.

Upvotes: 5

Views: 13326

Answers (2)

Frank Heikens
Frank Heikens

Reputation: 127367

Check the results from this query:

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

Upvotes: 10

Tom Studee
Tom Studee

Reputation: 10452

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;

Upvotes: 1

Related Questions