Невозможно удалить повторяющиеся строки с PostgreSQL

Question

Мой запрос удаляет всю таблицу вместо дублирующихся строк. Видео в доказательство: https://streamable.com/3s843

create table customer_info (
    id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone_number VARCHAR(50)
);
insert into customer_info (id, first_name, last_name, phone_number) values
(1, 'Kevin', 'Binley', '600-449-1059'),
(1, 'Kevin', 'Binley', '600-449-1059'),
(2, 'Skippy', 'Lam', '779-278-0889');

Мой запрос:

with t1 as (
select *, row_number() over(partition by id order by id) as rn
from customer_info)

delete
from customer_info 
where id in (select id from t1 where rn > 1);

Answer 1

Ваш запрос удалит все строки из каждого набора дубликатов (так как все разделяют одно и то же id с помощью которого вы выбираете - это то, на что @wildplasser намекает с тонкими комментариями) и только первоначально уникальные строки выживут. Так что, если он "удаляет всю таблицу", это означает, что уникальных строк вообще не было.

В вашем запросе дуплексы определяются (id) в одиночку, а не на весь ряд, как предполагает ваш заголовок.

В любом случае, есть удивительно простое решение:

DELETE FROM customer_info c
WHERE  EXISTS (
   SELECT FROM customer_info c1
   WHERE  ctid < c.ctid
   AND    c1 = c  -- comparing whole rows
   );

Поскольку вы имеете дело с полностью идентичными строками, оставшийся способ отличить их - внутренний идентификатор кортежа ctid,

Мой запрос удаляет все строки, где одинаковая строка с меньшим ctid существует. Следовательно, выживает только "первая" строка из каждого набора обманщиков.

Следует отметить, что NULL значения сравниваются в этом случае равными - что, скорее всего, соответствует желаемому. Руководство:

Спецификация SQL требует сравнения строк для возврата NULL, если результат зависит от сравнения двух значений NULL или NULL и не NULL. PostgreSQL делает это только при сравнении результатов двух конструкторов строк (как в Разделе 9.23.5) или при сравнении конструкторов строк с выходными данными подзапроса (как в Разделе 9.22). В других контекстах, где сравниваются два значения составного типа, два значения поля NULL считаются равными, [...]

Если обман определяется id в одиночку (как предполагает ваш запрос), тогда это будет работать:

DELETE FROM customer_info c
WHERE  EXISTS (
   SELECT FROM customer_info c1
   WHERE  ctid < c.ctid
   AND    id = c.id
   );

Но тогда может быть лучший способ решить, какие строки сохранить, чем ctid в качестве крайней меры!

Очевидно, вы бы тогда добавили PRIMARY KEY чтобы избежать первоначальной дилеммы от повторного появления. Для второй интерпретации id это кандидат.

Связанный:

• Как мне (или я могу) ВЫБРАТЬ DISTINCT по нескольким столбцам?

Около ctid:

• Как мне разложить ctid на номера страниц и строк?

Answer 2

Вы не можете, если у таблицы нет ключа.

Таблицы имеют "ключи", которые уникально идентифицируют каждую строку. Если в вашей таблице нет ключа, вы не сможете идентифицировать одну строку из другой.

Единственный обходной путь для удаления повторяющихся строк, о котором я могу подумать, заключается в следующем:

1. Добавьте ключ на стол.
2. Используйте клавишу, чтобы удалить лишние строки.

Например:

create sequence seq1;
alter table customer_info add column k1 int;
update customer_info set k1 = nextval('seq1');

delete from customer_info where k1 in (
  select k1 
  from (
    select
      k1,
      row_number() over(partition by id, first_name, last_name, phone_number) as rn
    from customer_info
  ) x
  where rn > 1
) 

Теперь у вас есть только два ряда.