I have a user table with the fields
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?
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