I'm using the official Heroku add-on for PostgreSQL and I have a database running on version 12.4 of PostgreSQL.

I'm facing an issue where on my users table I have an email like this fakeemail@domain.com and I want to add another one that is fake.email@domain.com. This email column has an associated index that was defined like this:

CREATE UNIQUE INDEX users_lower_email_index
    ON public.users USING btree
    (lower(email::text) COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

It basically assures that I am not able to insert the same email twice if I for some reason try to add the same email with upper case letters. Beside this index, the only other index is the primary key which is a GUID, and there are no triggers or other constraints.

Whenever I try to insert the fake.email@domain.com I get the following error:

ERROR:  duplicate key value violates unique constraint "users_lower_email_index"
DETAIL:  Key (lower(email::text))=(fake.email@domain.com) already exists.

I have queried this table numerous times and I can guarantee that such an email does not exist. So the next thing I assumed could be wrong, is that the index could be somehow corrupted. So I rebuilt the index but I still wasn't able to insert that email - the same error is thrown. I also tried vacuuming the table and then re-indexing the index again but it still didn't work with the same error.

I have no idea what could be wrong or what to try next.

  • 2
    run a query SELECT * FROM users WHERE lower(email::text) = 'fake.email@domain.com' and see where it gets you, rdms usually don't make misstakes – nbk Nov 20 '20 at 19:18
  • Omg, I can't believe I forgot to do that. I'm facepalming very hard. There was an exact email but with upper case letters, thank you. I jumped towards the more advanced stuff and forgot about that. I'd like to give you the correct answer if you create an answer. – João Paiva Nov 20 '20 at 19:22

rdms usually don't make mistakes

So run a query

SELECT * FROM users WHERE lower(email::text) = 'fake.email@domain.com' 

and see where it gets you a result, with upper letters

Not the answer you're looking for? Browse other questions tagged or ask your own question.