I have a Postgres 11 table on RDS containing a column email; some of the values in this column [and only that column] are clearly de facto duplicates but differ in case, i.e., different capitalization, such as:


To be clear, none of the present rows are true duplicates, nor share precisely the same values in that column. My objective is to identify these records [and, once found, eliminate/merge the de-facto duplicates].

My initial inclination was to use a self-join, e.g.:

SELECT c.email 
FROM schema.table c 
INNER JOIN schema.table d ON lower(c.email) = lower(d.email)
ORDER BY c.email;

However, this returns all the email records rather than only those that are de-facto-duplications.

Using a subquery such as the following produces a similar [i.e., too-inclusive] result:

SELECT c.email, alias.email 
FROM schema.table c 
  JOIN (SELECT email FROM schema.table) alias ON lower(c.email) = lower(alias.email);

Since I’m not looking for an aggregate, but rather a case-insensitive comparison, it seems to me that a window function is not the correct approach.

I think that this should be a straightforward query, but I’m having a difficult time seeing it clearly and am sure there is an error in the way I’m conceiving of the problem; it’s pretty frustrating.

In addition to searching here and on SO, I consulted Molinaro’s SQL Cookbook, but to no avail.

What is the correct way to structure the query so that it returns only those records whose email values are the same, disregarding case?

edit note : my initial question formulation expressed a misguided inclination to use ILIKE for case-insensitive matching, but the use of lower() as suggested in the below answers is far more sensible

2 Answers 2


Another case for EXISTS:

FROM   schema.table t
   SELECT FROM schema.table t1
   WHERE  lower(t.email) = lower(t1.email)
   AND    t.ctid <> t1.ctid
ORDER   BY lower(email), email;

If you have a PK, use it instead of ctid. Related:

This returns every qualifying row once. The added ORDER BY helps to keep dupes together and in deterministic sort order (unless your locale is case insensitive).

Why not use a simple join?

If you have, say, 10 variants of the same email, a simple join would give you 10 over 2 = 90 rows, and repeat every combination with reversed roles. Basically a limited Carthesian product of all dupes for each set of dupes.


A trigram index as suggested there should greatly help performance with tables of non-trivial size.

Also note that lower(t.email) = lower(t1.email) is slightly different from t.email ILIKE t1.email. The latter treats the right side as pattern, where some characters have special meaning unless you escape them. See:

  • Fantastic and crystal clear explanation -- worked an absolute treat -- thanks also for the tip re : trigram index -- will upvote when I have sufficient rep to do so Commented Dec 4, 2019 at 17:04
  • I had to add "and t.email<> t1.email" to WHERE condition, so that I can see the same in lowercase but different in original.
    – TomoMiha
    Commented Aug 23, 2023 at 9:59
  • @TomoMiha: WHERE t.ctid <> t1.ctid takes care of this while "none of the present rows [...] share precisely the same values in that column." Commented Aug 28, 2023 at 21:16

You need to exclude the rows from each other:

SELECT c.email
  FROM schema.table c,
       schema.table d
 WHERE lower(c.email) = lower(d.email)
   AND c.email <> d.email;

One question you will need to answer (and I will update my answer if relevant) -- are you using ILIKE because you want partial matches too? Like, are [email protected] and [email protected] supposed to be considered duplicates? (I would assume not, but when people use ILIKE, it's usually to patch parts of strings, as in the bert/robert example)

  • 1
    You probably want and c.primary_key_column <> d.primary_key_column
    – user1822
    Commented Dec 4, 2019 at 7:59
  • Brilliant! You're absolutely right, I had no reason to use ILIKE when I could have simply used lower(). Editing my question to reflect this. Thanks a whole bunch richyen and @a_horse_with_no_name -- will upvote when I have sufficient reputation to do so. Commented Dec 4, 2019 at 16:36
  • This starts multiplying rows if there can be more than two variants of the same email - and even returns each combination twice for just two - unless you use something like and c.primary_key_column > d.primary_key_column (instead of <>). I added an answer explaining why. Commented Dec 4, 2019 at 16:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.