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 [email protected] and [email protected] 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?

Jens · Accepted Answer · 2023-01-24 10:30:49Z

5

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

answered Jan 24, 2023 at 10:30

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to find duplicate case insensitive records in Postgresql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related