Как использовать физическое расположение строк (ROWID) в операторе DELETE

Question

У меня есть таблица с множеством дублированных строк и без первичного ключа.
Я хочу удалить только дублированные записи, но когда я попытаюсь это сделать, он удалит всех пиров.

Как я могу найти ROWID со стола в Postgres?

Answer 1

В PostgreSQL физическое местоположение строки называется CTID.

Так что, если вы хотите просмотреть его, используйте запрос как это:

SELECT CTID FROM table_name

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

DELETE FROM table_name WHERE CTID NOT IN (
  SELECT RECID FROM 
    (SELECT MIN(CTID) AS RECID, other_columns 
      FROM table_name GROUP BY other_columns) 
  a);

Помните, что table_name - это желаемая таблица, а other_columns - это столбцы, которые вы хотите использовать для фильтрации.

То есть:

DELETE FROM user_department WHERE CTID NOT IN (
  SELECT RECID FROM 
    (SELECT MIN(CTID) AS RECID, ud.user_id, ud.department_id
      FROM user_department ud GROUP BY ud.user_id, ud.department_id) 
  a);

Answer 2

Упростите это на один уровень запроса:

DELETE FROM table_name
WHERE  ctid NOT IN (
   SELECT min(ctid)
   FROM   table_name
   GROUP  BY $other_columns);

.. где дубликаты определяются равенством в $other_columns,
Нет необходимости включать столбцы из GROUP BY пункт в SELECT список, так что вам не нужен еще один подзапрос.

ctid в текущем руководстве.

Answer 3

Вы должны рассмотреть возможность использования row_number() если хотите удалить на основе уникального столбца идентификатора (или отметки времени), так как ctid одиночество не всегда надежно, если вы хотите вести только последние записи и т. д.

WITH d 
     AS (SELECT ctid c, 
                row_number() 
                  OVER ( 
                    partition BY s 
                    ORDER BY id) rn 
         FROM   t) 
DELETE FROM t 
WHERE  ctid IN (SELECT c 
               FROM   d 
               WHERE  rn > 1)  ; 

демонстрация