How to find duplicate records in PostgreSQL

The basic idea will be using a nested query with count aggregation:

select * from yourTable ouwhere (select count(*) from yourTable inrwhere inr.sid = ou.sid) > 1

You can adjust the where clause in the inner query to narrow the search.

There is another good solution for that mentioned in the comments, (but not everyone reads them):

select Column1, Column2, count(*)from yourTablegroup by Column1, Column2HAVING count(*) > 1

Or shorter:

SELECT (yourTable.*)::text, count(*)FROM yourTableGROUP BY yourTable.*HAVING count(*) > 1

From "Find duplicate rows with PostgreSQL" here's smart solution:

select * from (  SELECT id,  ROW_NUMBER() OVER(PARTITION BY column1, column2 ORDER BY id asc) AS Row  FROM tbl) dupswhere dups.Row > 1

In order to make it easier I assume that you wish to apply a unique constraint only for column year and the primary key is a column named id.

In order to find duplicate values you should run,

SELECT year, COUNT(id)FROM YOUR_TABLEGROUP BY yearHAVING COUNT(id) > 1ORDER BY COUNT(id);

Using the sql statement above you get a table which contains all the duplicate years in your table. In order to delete all the duplicates except of the the latest duplicate entry you should use the above sql statement.

DELETEFROM YOUR_TABLE A USING YOUR_TABLE_AGAIN BWHERE A.year=B.year AND A.id<B.id;