CODEWITHSUNDEEP
sqlpostgresqlindexing
michuk
michuk

Reputation: 1432

Could not create unique index - claims to have duplicate values but it doesn't

I'm trying to create a unique index on code field:

$ CREATE UNIQUE INDEX "one_code_per_person" on "core_person"("code") WHERE "code" IS NOT NULL;
ERROR:  could not create unique index "one_code_per_person"
DETAIL:  Table contains duplicated values.

It says there are duplicate values but:

$ select code, count(*) from core_person group by code having count(*)>2;
code | count 
-----------+-------
(0 rows)

WTF?

Upvotes: 3

Views: 3945

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332681

This:

HAVING COUNT(*) > 2

...means there needs to be 3+ duplicates. You want to use:

HAVING COUNT(*) > 1

...to find records with 2+ duplicates.

Upvotes: 10

Related Questions