solo - Cómo encontrar registros duplicados en PostgreSQL

traer registros repetidos postgresql

Question

Tengo una tabla de base de datos PostgreSQL llamada "user_links" que actualmente permite los siguientes campos duplicados:

year, user_id, sid, cid

La restricción única es actualmente el primer campo llamado "id", sin embargo, ahora estoy buscando agregar una restricción para asegurarme de que el year , user_id , sid y cid sean únicos, pero no puedo aplicar la restricción porque ya existen valores duplicados que violan esto restricción.

¿Hay alguna manera de encontrar todos los duplicados?

Answer 1

De " Buscar filas duplicadas con PostgreSQL " aquí hay una solución inteligente:

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

Answer 2

La idea básica será usar una consulta anidada con agregación de conteo:

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

Puede ajustar la cláusula where en la consulta interna para acotar la búsqueda.

Hay otra buena solución para eso mencionada en los comentarios (pero no todos los leen):

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

O más corto:

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

Answer 3

Puede unirse a la misma tabla en los campos que se duplicarían y luego unirse en el campo id. Seleccione el campo id del primer alias de la tabla (tn1) y luego use la función array_agg en el campo id del segundo alias de la tabla. Finalmente, para que la función array_agg funcione correctamente, agrupará los resultados por el campo tn1.id. Esto producirá un conjunto de resultados que contiene la identificación de un registro y una matriz de todas las identificaciones que se ajustan a las condiciones de unión.

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;

Obviamente, los ID que estarán en la matriz duplicate_entries para un ID también tendrán sus propias entradas en el conjunto de resultados. Tendrá que usar este conjunto de resultados para decidir qué identificación desea que se convierta en la fuente de la ''verdad''. El único registro que no debería eliminarse. Tal vez podrías hacer algo como esto:

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)

Selecciona las ID de números más bajos que tienen duplicados (suponiendo que la ID aumenta int PK). Estas serían las identificaciones que conservaría.