How to find duplicate records in PostgreSQL

Question

I have a PostgreSQL database table called  user links  which currently allows the following duplicate fields   year  user id  sid  cid   The unique constraint is currently the first field called  id   however I am now looking to add a constraint to make sure the year  user id  sid and cid are all unique but I cannot apply the constraint because duplicate values already exist which violate this constraint   Is there a way to find all duplicates

User · Answer

In order to make it easier I assume that you wish to apply a unique constraint only for column year and the primary key is a column named id.

In order to find duplicate values you should run,

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

Using the sql statement above you get a table which contains all the duplicate years in your table. In order to delete all the duplicates except of the the latest duplicate entry you should use the above sql statement.

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

User · Answer

In your case  because of the constraint you need to delete the duplicated records   Find the duplicated rows Organize them by created at date - in this case I m keeping the oldest Delete the records with USING to filter the right rows  WITH duplicated AS        SELECT id          count         FROM products      GROUP BY id      HAVING count     gt  1    ordered AS        SELECT p id           created at           rank   OVER  partition BY p id ORDER BY p created at  AS rnk      FROM products o      JOIN     duplicated d ON d id   p id     products to delete AS        SELECT id           created at      FROM   ordered      WHERE  rnk   2    DELETE  FROM products  USING products to delete  WHERE products id   products to delete id      AND products created at   products to delete created at

User · Answer

You can join to the same table on the fields that would be duplicated and then anti-join on the id field  Select the id field from the first table alias  tn1  and then use the array agg function on the id field of the second table alias  Finally  for the array agg function to work properly  you will group the results by the tn1 id field  This will produce a result set that contains the the id of a record and an array of all the id s that fit the join conditions   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  lt  gt  tn2 id group by tn1 id    Obviously  id s that will be in the duplicate entries array for one id  will also have their own entries in the result set  You will have to use this result set to decide which id you want to become the source of  truth   The one record that shouldn t get deleted  Maybe you could do something like this   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  lt  gt  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  lt  ds id    Selects the lowest number ID s that have duplicates  assuming the ID is increasing int PK   These would be the ID s that you would keep around

User · Answer

The basic idea will be using a nested query with count aggregation   select   from yourTable ou where  select count    from yourTable inr where inr sid   ou sid   gt  1   You can adjust the where clause in the inner query to narrow the search     There is another good solution for that mentioned in the comments   but not everyone reads them    select Column1  Column2  count    from yourTable group by Column1  Column2 HAVING count     gt  1   Or shorter   SELECT  yourTable     text  count    FROM yourTable GROUP BY yourTable   HAVING count     gt  1

User · Answer

From  Find duplicate rows with PostgreSQL  here s smart solution   select   from     SELECT id    ROW NUMBER   OVER PARTITION BY column1  column2 ORDER BY id asc  AS Row   FROM tbl   dups where  dups Row  gt  1

[sql] How to find duplicate records in PostgreSQL

Examples related to sql

Examples related to postgresql

Examples related to duplicates