How to find duplicate case insensitive records in Postgresql?

Question

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 user u1
where (select count(*) from user u2
where LOWER(u1.email) = LOWER(u2.email)) > 1

I am finding plenty of examples to find literal duplicate records, but nothing for case-insensitive duplicates. Any ideas?

Answer 1

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

DEMO