Find and delete duplicate rows with PostgreSQL

Question

We have a table of photos with the following columns:

id, merchant_id, url

this table contains duplicate values for the combination merchant_id, url. so it's possible that one row appears more several times.

234 some_merchant  http://www.some-image-url.com/abscde1213
235 some_merchant  http://www.some-image-url.com/abscde1213
236 some_merchant  http://www.some-image-url.com/abscde1213

What is the best way to delete those duplications? (I use PostgreSQL 9.2 and Rails 3.)

Is your ID column unique? I see 234 3 times but you say your merchant_id and url are the duplicate values. — sgeddes, Jan 23 '13 at 02:27
Possible duplicate of http://stackoverflow.com/questions/1746213/how-to-delete-duplicate-entries-in-postgresql — , Jan 23 '13 at 02:51
sorry for the confusion. the id in the example above should be unique. thanks for the correct edit. the solution here stackoverflow.com/questions/1746213/… doesn't work for my case. — schlubbi, Jan 23 '13 at 08:26

score 147 · Accepted Answer · edited Apr 14 '17 at 17:20

147

Here is my take on it.

select * from (
  SELECT id,
  ROW_NUMBER() OVER(PARTITION BY merchant_Id, url ORDER BY id asc) AS Row
  FROM Photos
) dups
where 
dups.Row > 1

Feel free to play with the order by to tailor the records you want to delete to your specification.

SQL Fiddle => http://sqlfiddle.com/#!15/d6941/1/0

SQL Fiddle for Postgres 9.2 is no longer supported; updating SQL Fiddle to postgres 9.3

edited Apr 14 '17 at 17:20

the Tin Man

158,662
42
215
303

answered Jan 23 '13 at 03:21

MatthewJ

3,127
2
27
34

3

This works like a charm but how do you delete the duplicates found using this query ? – Trasplazio Garzuglio Mar 28 '14 at 13:44
1

If we have same thing repeating 3 times, the take 2 and take 3 is taken in result. How can i resolve it? – Fausto Carvalho Marques Silva Mar 07 '16 at 14:24
It's a bit unclear about what you are trying to do; the answer provided shows, using the sql fiddle link, how to delete rows based on columns defined. You should probably open up a new question with specific steps to reproduce. – MatthewJ Mar 07 '16 at 14:54
Could you explain what it is actually doing by `ROW_NUMBER() OVER(PARTITION BY merchant_Id, url ORDER BY id asc)` – Ganbayar Gansukh Jul 15 '16 at 14:01
won't this delete all records with the duplicate id's? I think de OP wants to keep one record in – Daniël Tulp Mar 28 '17 at 10:48
1

No it will not, that's precisely why you check rows > 1. See the sql fiddle. – MatthewJ Mar 29 '17 at 21:33
1

As a non db guy I found this explanationa really good https://www.postgresqltutorial.com/postgresql-row_number/ – Miguel Lattuada Jul 18 '20 at 18:29
to delete rows: http://sqlfiddle.com/#!15/d6941/983 – Schulzjo Sep 26 '22 at 17:31

11101101b · Answer 2 · 2015-03-11T20:39:35.680

10

The second part of sgeddes's answer doesn't work on Postgres (the fiddle uses MySQL). Here is an updated version of his answer using Postgres: http://sqlfiddle.com/#!12/6b1a7/1

DELETE FROM Photos AS P1  
USING Photos AS P2
WHERE P1.id > P2.id
   AND P1.merchant_id = P2.merchant_id  
   AND P1.url = P2.url;

edited Mar 11 '15 at 20:39

answered Mar 10 '15 at 21:14

11101101b

7,679
2
42
52

score 6 · Answer 3 · edited Apr 14 '17 at 17:20

6

I see a couple of options for you.

For a quick way of doing it, use something like this (it assumes your ID column is not unique as you mention 234 multiple times above):

CREATE TABLE tmpPhotos AS SELECT DISTINCT * FROM Photos;
DROP TABLE Photos;
ALTER TABLE tmpPhotos RENAME TO Photos;

Here is the SQL Fiddle.

You will need to add your constraints back to the table if you have any.

If your ID column is unique, you could do something like to keep your lowest id:

DELETE FROM P1  
USING Photos P1, Photos P2
WHERE P1.id > P2.id
   AND P1.merchant_id = P2.merchant_id  
   AND P1.url = P2.url;

And the Fiddle.

edited Apr 14 '17 at 17:20

the Tin Man

158,662
42
215
303

answered Jan 23 '13 at 02:50

sgeddes

62,311
6
61
83

2

the id is unique in my case. I just did it wrong in my example code. but I get an error if I try to use your second solution. `ERROR: relation "p1" does not exist` – schlubbi Jan 23 '13 at 08:05
@StefanSchmidt I fixed it to run on Postgres instead of MySQL: http://sqlfiddle.com/#!12/6b1a7/1 – 11101101b Mar 10 '15 at 21:12

Find and delete duplicate rows with PostgreSQL

3 Answers3

Linked