Jak znaleźć zduplikowane rekordy w PostgreSQL

Mam tabelę bazy danych PostgreSQL o nazwie „user_links”, która obecnie pozwala na następujące zduplikowane pola:

year, user_id, sid, cid

Unikalna ograniczenie jest obecnie pierwsze pole o nazwie „id”, jednak jestem teraz chce dodać ograniczenie, aby upewnić się year, user_id, sida cidwszystkie są wyjątkowe, ale nie mogę zastosować ograniczenia, ponieważ już istnieją zduplikowane wartości, które naruszają te ograniczenia.

Czy istnieje sposób na znalezienie wszystkich duplikatów?

Podstawową ideą będzie użycie zagnieżdżonego zapytania z agregacją liczby:

select * from yourTable ou
where (select count(*) from yourTable inr
where inr.sid = ou.sid) > 1

Możesz dostosować klauzulę where w wewnętrznym zapytaniu, aby zawęzić wyszukiwanie.

Istnieje inne dobre rozwiązanie tego wspomnianego w komentarzach (ale nie wszyscy je czytają):

select Column1, Column2, count(*)
from yourTable
group by Column1, Column2
HAVING count(*) > 1

Lub krócej:

SELECT (yourTable.*)::text, count(*)
FROM yourTable
GROUP BY yourTable.*
HAVING count(*) > 1

Od „ Znajdź zduplikowane wiersze za pomocą PostgreSQL ” oto inteligentne rozwiązanie:

select * from (
  SELECT id,
  ROW_NUMBER() OVER(PARTITION BY column1, column2 ORDER BY id asc) AS Row
  FROM tbl
) dups
where 
dups.Row > 1

Możesz dołączyć do tej samej tabeli na polach, które zostałyby zduplikowane, a następnie anty-dołączyć na polu id. Wybierz pole id z pierwszego aliasu tabeli (tn1), a następnie użyj funkcji array_agg w polu id drugiego aliasu tabeli. Wreszcie, aby funkcja array_agg działała poprawnie, wyniki zostaną pogrupowane według pola tn1.id. Spowoduje to wygenerowanie zestawu wyników zawierającego identyfikator rekordu i tablicę wszystkich identyfikatorów pasujących do warunków łączenia.

select tn1.id,
       array_agg(tn2.id) as duplicate_entries, 
from table_name tn1 join table_name tn2 on 
    tn1.year = tn2.year 
    and tn1.sid = tn2.sid 
    and tn1.user_id = tn2.user_id 
    and tn1.cid = tn2.cid
    and tn1.id <> tn2.id
group by tn1.id;

Oczywiście identyfikatory, które będą w tablicy duplicate_entries dla jednego identyfikatora, również będą miały własne wpisy w zestawie wyników. Będziesz musiał użyć tego zestawu wyników, aby zdecydować, który identyfikator chcesz stać się źródłem „prawdy”. Jeden rekord, którego nie należy usuwać. Może mógłbyś zrobić coś takiego:

with dupe_set as (
select tn1.id,
       array_agg(tn2.id) as duplicate_entries, 
from table_name tn1 join table_name tn2 on 
    tn1.year = tn2.year 
    and tn1.sid = tn2.sid 
    and tn1.user_id = tn2.user_id 
    and tn1.cid = tn2.cid
    and tn1.id <> tn2.id
group by tn1.id
order by tn1.id asc)
select ds.id from dupe_set ds where not exists 
 (select de from unnest(ds.duplicate_entries) as de where de < ds.id)

Wybiera najniższe numery identyfikatorów, które mają duplikaty (zakładając, że identyfikator zwiększa int PK). Byłyby to identyfikatory, które miałbyś przy sobie.

Aby to ułatwić, zakładam, że chcesz zastosować unikalne ograniczenie tylko dla roku kolumny, a kluczem podstawowym jest kolumna o nazwie id.

Aby znaleźć zduplikowane wartości, powinieneś uruchomić,

SELECT year, COUNT(id)
FROM YOUR_TABLE
GROUP BY year
HAVING COUNT(id) > 1
ORDER BY COUNT(id);

Używając powyższej instrukcji sql, otrzymujesz tabelę, która zawiera wszystkie zduplikowane lata w twojej tabeli. Aby usunąć wszystkie duplikaty oprócz najnowszej zduplikowanej pozycji , powinieneś użyć powyższej instrukcji sql.

DELETE
FROM YOUR_TABLE A USING YOUR_TABLE_AGAIN B
WHERE A.year=B.year AND A.id<B.id;