Why can't I insert an email on a table that has one more dot than an existing one? 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.&quot;default&quot; 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 &quot;users_lower_email_index&quot;
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.
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