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
firstname.lastname@example.org and I want to add another one that is
email@example.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
firstname.lastname@example.org I get the following error:
ERROR: duplicate key value violates unique constraint "users_lower_email_index" DETAIL: Key (lower(email::text))=(email@example.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.