PostgreSQL：基于MD5哈希匹配删除重复行

问题根源 #

你遇到的column reference "column1" is ambiguous错误，是因为SQL里同时引用了my_table的两个别名a和b，但调用md5()时没指定column1属于哪个别名的表，数据库没法判断你要取哪张表的列。

修正后的SQL #

给column1加上对应的表别名，明确指定归属即可：

DELETE FROM
    my_table a
USING my_table b
WHERE
    a.pk < b.pk
    AND md5(a.column1::text) = md5(b.column1::text);

如果column1本身不是文本类型，显式转成text能避免隐式转换带来的意外问题。另外如果要避免处理NULL值（md5(NULL)会返回NULL，无法匹配），可以加上非空判断：

DELETE FROM
    my_table a
USING my_table b
WHERE
    a.pk < b.pk
    AND a.column1 IS NOT NULL
    AND b.column1 IS NOT NULL
    AND md5(a.column1::text) = md5(b.column1::text);

额外说明 #

• 当前逻辑会保留pk值更大的行，删除更早的重复行；如果要反过来保留旧数据，把a.pk < b.pk改成a.pk > b.pk就行。
• 要是需要基于多列判断重复，直接在WHERE条件里追加对应列的MD5比较即可，比如AND md5(a.column2::text) = md5(b.column2::text)。

内容的提问来源于stack exchange，提问作者Nathan McIntosh