Удалить дубликаты записей из таблицы Postgresql без первичного ключа?

У меня есть таблица типа

CREATE TABLE meta.fk_payment1
(
  id serial NOT NULL,
  settlement_ref_no character varying,
  order_type character varying,
  fulfilment_type character varying,
  seller_sku character varying,
  wsn character varying,
  order_id character varying,
  order_item_id bigint,
   ....
  );

Я вставляю данные из файла csv, где все столбцы одинаковы, а не id column

В случае, если файл csv загружен более одного раза, данные будут дублироваться.

но id не будет, а id будет первичным.

поэтому я хочу удалить всю повторяющуюся строку без использования первичного ключа.

Мне нужно сделать это на отдельной таблице

Ответ 1

Скопируйте отдельные данные в рабочую таблицу fk_payment1_copy. Самый простой способ сделать это - использовать into

SELECT max(id),settlement_ref_no ... 
INTO fk_payment1_copy
from fk_payment1
GROUP BY settlement_ref_no ...

удалить все строки из fk_payment1

delete from fk_payment1

и скопируйте данные из таблицы fk_payment1_copy в fk_payment1

insert into fk_payment1
select id,settlement_ref_no ... 
from fk_payment1_copy

Ответ 2

Вы можете сделать это например.

DELETE FROM table_name
    WHERE   ctid NOT IN
    (SELECT     MAX(dt.ctid)
        FROM        table_name As dt
        GROUP BY    dt.*);

запустите этот запрос

DELETE FROM meta.fk_payment1
    WHERE   ctid NOT IN
    (SELECT     MAX(dt.ctid)
        FROM        meta.fk_payment1 As dt
        GROUP BY    dt.*);

Ответ 3

Немного не уверен в части первичного ключа в вопросе, но в любом случае id не обязательно должен быть первичным ключом, он просто должен быть уникальным. Как и должно быть, так как он серийный. Поэтому, если он имеет уникальные значения, вы можете сделать это следующим образом:

DELETE FROM fk_payment1 f WHERE EXISTS
  (SELECT * FROM fk_payment1 WHERE id<f.id
   AND settlement_ref_no=f.settlement_ref_no
   AND ...)

Просто нужно добавить все столбцы в запрос выбора. Таким образом будут удалены все строки с одинаковыми значениями (кроме id) и после этой строки (отсортированные по id).

(Именование таблицы с префиксом fk_ делает ее похожим на внешний ключ.)

Ответ 4

если таблица не очень велика, вы можете сделать:

-- create temporary table and select distinct into it. 
CREATE TEMP TABLE tmp_table AS 
SELECT DISTINCT column_1, column_2 
FROM original_table ORDER BY column_1, column_2;

-- clear the original table 
TRUNCATE original_table;

-- copy data back in again 
INSERT INTO original_table(column_1, column_2) 
SELECT * FROM  tmp_table ORDER BY column_1, column_2;

-- clean up
DROP TABLE tmp_table

для больших таблиц удалите команду TEMP из создания tmp_table
Это решение пригодится при работе с JPA (Hibernate), созданным @ElementCollection, которые создаются без первичного ключа.

Ответ 5

Таким образом, в PG wiki есть пятно. https://wiki.postgresql.org/wiki/Deleting_duplicates

Этот запрос делает это для всех строк tablename, имеющих одинаковые столбцы1, column2 и column3.

DELETE FROM tablename
WHERE id IN (SELECT id
              FROM (SELECT id,
                             ROW_NUMBER() OVER (partition BY column1, column2, column3 ORDER BY id) AS rnum
                     FROM tablename) t
              WHERE t.rnum > 1);

Я тестировал это при снятии дуплексных 600k строк, что привело к 200k уникальным строкам. Решение с использованием group by и NOT IN заняло 3h +, это занимает примерно 3 секунды.