I have a user table with the fields id and email (among others). The email address isn’t case insensitive, a problem we want to fix, but we have some duplicate values in there (duplicate except the case, so currently we have user@example.com and User@example.com in there). I am now trying to get an overview of all the duplicate accounts, but this query is just taking forever, I had to cancel it after 5 minutes. The table has about 250.000 records.
select * from account_emailaddress ae1
where (select count(*) from account_emailaddress ae2
where LOWER(ae1.email) = LOWER(ae2.email)) > 1
I am finding plenty of examples to find literal duplicate records, but nothing for case-insensitive duplicates. Any ideas?
>Solution :
You can use the having clause. Should be faster than the inner clause
select lower(email)
from test
group by lower(email)
having count(*)>1