I have a Postgres 11 table on RDS containing a column
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
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