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

Question

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)

which proves there are not.

WTF?

Actually, count(*)>1 would be the condition you need to test. — Dark Falcon
– Dark Falcon, Commented Jan 18, 2011 at 21:14

OMG Ponies · Accepted Answer · 2011-01-18 21:14:37Z

10

This:

HAVING COUNT(*) > 2

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

HAVING COUNT(*) > 1

...to find records with 2+ duplicates.

answered Jan 18, 2011 at 21:14

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related