Skip to content

Instantly share code, notes, and snippets.

@t27

t27/find-duplicates-query.sql

Last active July 2, 2019 11:29
Show Gist options
  • Save t27/91d340515045c8c62e6b00cc0aec64d2 to your computer and use it in GitHub Desktop.
Save t27/91d340515045c8c62e6b00cc0aec64d2 to your computer and use it in GitHub Desktop.
Download ZIP
Find rows in a table that have duplicate values for columns x,y,z,w. Also optionally aggregate another column.
Raw
find-duplicates-query.sql
--Postgres
--just find duplicates
select col1,col2,col3,col4,col5,count(*) from my_table group by 1,2,3,4,5,6 HAVING count(*)>1
-- find duplicates and aggregate Ids, so that you can save one and delete the rest ('id' is a column in the table)
select col1,col2,col3,col4,col5,count(*), string_agg(id::character varying, ';' order by sign_code) from my_table group by 1,2,3,4,5,6 HAVING count(*)>1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment