PostgreSQL: удаление повторяющихся строк на основе совпадающих хешей md5
У меня есть таблица, заполненная данными, полученными из Интернета. После добавления новых данных в таблицу я хочу удалить дубликаты, если в определенные столбцы не было внесено никаких изменений.
Я попробовал следующий запрос вместе с различными способами ссылки на хэш md5 и преобразование столбца1 в текст:
DELETE FROM
my_table a
USING my_table b
WHERE
a.pk < b.pk
AND a.md5(column1) = b.md5(column1);
и получите следующую ошибку:
ERROR: column reference "column1" is ambiguous LINE 6: AND a.md5(column1) = b.md5(column1); ^ SQL state: 42702 Character: 99
@Сорен большое спасибо, проблема решена, спасибо за столь быстрый ответ
Попробуйте a.column1=b.column1 — это будет быстрее и надежнее, если вы позволите Postgres разобраться в деталях.
Ответы 1
Не делайте дедупликацию на основе md5(), просто используйте =
Пусть Postgres выяснит, имеет ли он вообще выгоду от хеширования, и если да, то какой должна быть оптимальная функция хеширования. Вам легче объявлять, Postgres легче оптимизировать и выполнять. SQL задуман как декларативный — укажите, что вы хотите, а не как именно это должно быть сделано.
Таким образом вы также избежите потенциально неверных результатов — md5 используется просто для простоты, но долгое время считался ненадежным. Редко, но не невозможно, чтобы ваши значения были совершенно разными, но имели один и тот же md5, поэтому вы можете получить незаслуженное удаление.
Если Postgres обнаружит, что значения слишком длинные для эффективного сравнения с простым =, он незаметно изменит равенство значений на равенство хеш-значений, используя хэш-функцию по своему выбору:
explain analyze verbose
DELETE FROM my_table a
USING my_table b
WHERE a.pk < b.pk
AND a.column1 = b.column1
returning *;
Обратите внимание, как версия с md5 влияет на такое же количество строк, только в 4 раза медленнее: демо
explain analyze verbose
DELETE FROM my_table a
USING my_table b
WHERE a.pk < b.pk
AND md5(a.column1) = md5(b.column1)
returning *;
В качестве бонуса, если будущие версии Postgres будут улучшены с точки зрения хэш-операций, вы получите это бесплатно с =, без необходимости повторно обращаться и обновлять какой-либо код.
Отличное спасибо за ответ и демо. У меня есть база данных шириной 75 столбцов, и я хочу сохранить новую запись, если какая-либо из 8 ключевых записей изменилась. Будет ли это масштабироваться линейным образом?
Мне нужен пример описанного вами сравнения/дедупликации (я не совсем понял идею ни одной из 8 ключевых записей в этом контексте), но да, вы можете ожидать, что это будет линейно масштабироваться.
Попробуйте: AND md5(a.column1) = md5(b.column1);