remove duplicate records in postgres where all records are duplicate

Question

My postgres table model have exactly duplicate record, I need to write a query to delete them.

id | model | model_id | dependent_on_model

-----+-------+----------+--------------------

using Cte is not helping as i am getting the error relation "cte" does not exist.

Please suggest a query which delete the duplicate row and i will have just 4 distinct records at the end.

yes, so that at the end i will have just the distinct record. — user7097216
– user7097216, Commented Apr 1, 2019 at 15:46

J Spratt · Accepted Answer · 2019-04-01 17:10:28Z

0

My suggestion is to duplicate the table in a TEMPORARY TABLE WITH OIDS. This way you have some other id to distinguish the two identical rows.

Idea:

Duplicate the data with another ID in a temporary table.
Remove duplicates in temporary table.
Delete actual table
Copy data back into actual table from temporary table.
Delete the TEMPORARY TABLE

You'll have to perform some destructive action on your actual table so make sure your TEMPORARY TABLE has what you want remaining before deleting anything from your actual table.

This is how you would create the TEMPORARY TABLE:

CREATE TEMPORARY TABLE dups_with_oids
( id        integer
, model     text
, model_id  integer
, dependent_on_model text
) WITH OIDS;

Here is the DELETE query:

WITH temp AS
(
  SELECT  d.id AS keep
          , d.oid AS keep_oid
          , d2.id AS del
          , d2.oid AS del_oid
  FROM dups_with_oids d
    JOIN dups_with_oids d2 ON (d.id = d2.id AND d.oid < d2.oid)
) 
DELETE FROM dups_with_oids d
WHERE d.oid IN (SELECT temp.del_oid FROM temp);

SQLFiddle to prove the theory.

I should add that if id were a PRIMARY KEY or UNIQUE these duplicates wouldn't have been possible.

edited Apr 1, 2019 at 17:10

answered Apr 1, 2019 at 16:00

J Spratt

2,0521 gold badge16 silver badges24 bronze badges

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

2 Comments

user7097216 Over a year ago

Thanks for your suggestion, i used the above method but with different code logic as below. reate table metric_temp as table metric; delete from metric; INSERT INTO metric(id,table_id,creator_id,name,description,archived,definition,created_at,updated_at,points_of_interest,caveats,how_is_this_calculated,show_in_getting_started) SELECT DISTINCT ON (id) id, table_id,creator_id,name,description,archived,definition,created_at,updated_at,points_of_interest,caveats,how_is_this_calculated,show_in_getting_started FROM metric_temp; drop table metric_temp;

J Spratt Over a year ago

You're welcome. I like your DISTINCT method also. Probably quite a few ways to solve the problem. Good luck!

Collectives™ on Stack Overflow

remove duplicate records in postgres where all records are duplicate

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related