How to delete duplicate rows for 2 columns with PostgreSQL?

Question

Table DDL:

CREATE TABLE entities_patents (
    entity_id INTEGER REFERENCES entities(entity_id) NOT NULL,
    patent_id INTEGER REFERENCES patents (patent_id) NOT NULL,
    doc_number text NOT NULL,
    created_at timestamp with time zone DEFAULT current_timestamp,
    updated_at timestamp with time zone DEFAULT current_timestamp,
    deleted boolean,
    PRIMARY KEY (entity_id, patent_id)
);

Unfortunately there are duplicates in the table and I could not add the pk constraint previously. Trying to do so now leads to:

DETAIL:  Key (entity_id, patent_id)=(123123, 811231333) is duplicated.

Is there a way to delete these duplicates?

sagi · Accepted Answer · 2016-03-09 11:11:19Z

1

You can use ROW_NUMBER(), so try this:(untested so may have some syntax issues)

DELETE FROM entities_patents t
WHERE EXISTS(SELECT * FROM (
                    SELECT p.entity_id,p.patent_id,p.created_at,p.updated_at,
                    ROW_NUMBER() OVER(PARTITION BY p.entity_id,p.patent_id ORDER BY p.created_at DESC,p.updated_at DESC) as rnk
                    FROM entities_patents p) s
              WHERE s.rnk > 1 and s.entity_id = t.entity_id and s.patent_id = t.patent_id
                  AND s.created_at = t.created_at and s.updated_at = t.updated_at)

answered Mar 9, 2016 at 11:11

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Praveen · Accepted Answer · 2016-03-09 11:18:10Z

1

Another method by using group by and count

DELETE 
FROM entities_patents B  
USING (
      select entity_id, patent_id
      from entities_patents
      group by entity_id, patent_id
      having count(*) > 1
) C 
WHERE B.entity_id = C.entity_id AND
      B.patent_id = C.patent_id

answered Mar 9, 2016 at 11:18

Praveen

9,4634 gold badges38 silver badges51 bronze badges

Comments

Mihai · Accepted Answer · 2016-03-11 23:31:37Z

1

WITH CTE AS
  (SELECT *,ROW_NUMBER() OVER(PARTITION by entity_id, patent_id
                              ORDER BY created_at) AS rn
   FROM entities_patents)
DELETE
FROM entities_patents
WHERE (entity_id,patent_id) IN (SELECT entity_id,patent_id FROM CTE WHERE rn >1)

edited Mar 11, 2016 at 23:31

answered Mar 9, 2016 at 11:14

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Collectives™ on Stack Overflow

How to delete duplicate rows for 2 columns with PostgreSQL?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related