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

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.