CODEWITHSUNDEEP
databasepostgresqlindexinguniqueunique-index
daryn
daryn

Reputation: 926

Why do I have non-unique entries against a unique index? (PostgreSQL 9.0)

I have a unique index in my postgresql 9.0 db, based on a function. I have yet to make it fail with any manual tests I try, but I am seeing some duplicates in the db when I query it.

Check this out:

Index: "users_screen_name_idx" UNIQUE, btree (lower(screen_name::text))

# select lower(screen_name), count(1) from users group by lower(screen_name) having count(1) > 1;

 lower         | count 
---------------+-------
 xxx xxx 3735  |     2
 xxx xxx 37383 |     2
 ... (36 more) ...
               | 17254
(39 rows)

Any ideas how this is happening? I know NULL's aren't unique, that's not the issue, it's the other 38 rows.

Upvotes: 2

Views: 250

Answers (3)

Chris Travers
Chris Travers

Reputation: 26464

If you are using table inheritance, that can cause this since unique constraints are not inherited. In fact this is probably the most common cause of this sort of thing and it isn't data corruption.

If you drop and recreate the index does it fail?

If not, your duplication is elsewhere.

Upvotes: 0

Daniel
Daniel

Reputation: 28084

You can disable triggers in PostgreSQL. This is a very dangerous option, but can be used to actually add data into tables that violates unique or foreign key contraints.

Upvotes: 0

Peter Eisentraut
Peter Eisentraut

Reputation: 36739

If you have been manually trying to make the database system fail, it's possible that you have run into index corruption. Try rebuilding the index (REINDEX). If that fails because of duplicate values, then that's it.

Upvotes: 1

Related Questions