Find duplicate rows with PostgreSQL

June 8, 2023 by Tarik

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