在單個表中查找特定列中的值幾乎重複的行

我在 RDS 上有一個 Postgres 11 表，其中包含一列email；此列中的一些值

$$ and only that column $$顯然是事實上的重複，但大小寫不同，即不同的大小寫，例如：

foo@****.com
Foo@****.com

需要明確的是，目前的行都不是真正的重複，也不是在該列中共享完全相同的值。我的目標是辨識這些記錄

$$ and, once found, eliminate/merge the de-facto duplicates $$. 我最初的傾向是使用自連接，例如：

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

但是，這會返回所有電子郵件記錄，而不僅僅是那些事實上的重複記錄。

使用如下子查詢會產生類似的

$$ i.e., too-inclusive $$結果：

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

由於我不是在尋找一個聚合，而是一個不區分大小寫的比較，所以在我看來，視窗函式不是正確的方法。

我認為這應該是一個直截了當的查詢，但我很難清楚地看到它，並且我確信我在構思問題的方式上存在錯誤；這很令人沮喪。

除了在這里和 SO 上搜尋之外，我還查閱了 Molinaro 的SQL Cookbook，但無濟於事。

構造查詢的正確方法是什麼，以便它只返回那些email值相同的記錄，而不考慮大小寫？

編輯說明：我最初的問題表述表達了使用 ILIKE 進行不區分大小寫匹配的錯誤傾向，但是按照以下答案中的建議使用 lower() 更為明智

另一個案例EXISTS：

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

如果您有 PK，請使用它而不是ctid. 有關的：

• 如何刪除 PostgreSQL 連接表中的重複記錄？

這將返回每個符合條件的行一次。添加的內容ORDER BY有助於將欺騙者保持在一起並保持確定的排序順序（除非您的語言環境不區分大小寫）。

為什麼不使用簡單的連接？ 例如，如果您有同一封電子郵件的 10 個變體，一個簡單的連接將給您10 over 2 = 90 行，並以相反的角色重複每個組合。基本上是每組騙子的所有騙子的有限Carthesian 乘積。

有關的：

• LOWER LIKE vs iLIKE

那裡建議的三元組索引應該極大地幫助處理非平凡大小的表的性能。

另請注意，lower(t.email) = lower(t1.email)與 略有不同t.email ILIKE t1.email。後者將右側視為模式，其中某些字元具有特殊含義，除非您對其進行轉義。看：

• 正則表達式或 LIKE 模式的轉義函式

引用自：https://dba.stackexchange.com/questions/254722