Tisha Tisha - 27 days ago 6
SQL Question

How do I delete one row for two duplicate entries in a postgres table?

The two rows have all same columns except a timestamp column - created_at

I just want to retain one of these rows - doesn't matter which.

This is how I am able to select one of each of these duplicate rows that I can delete on the basis of the created_at column which has the lesser value.

select e.id, e.user_id, e.r_needed, e.match, e.status, e.stage, e.created_at
from employee e, employee e2 where e.id=e2.id and e.user_id=e2.user_id and
e.status = 12 and e2.status=12 and e.stage=false and e2.stage=false and
e.match=true and e2.match=true and e.user_id=12 and e.r_needed=true and e2.r_needed=true
and e.created_at<e2.created_at and DATE(e.created_at)='2015-10-08';


However, cannot figure how I can delete this row such that both of the duplicates do not get deleted and only the ones that are selected above do?

Basically, I want to delete all rows that match the columns in my select query above and the row which has the lesser value for created_at.

My table has no primary key or unique key.

Answer

You can use a Correlated Subquery instead of a join:

select * from employee e
where exists
 ( select * from employee e2 
   where e.id=e2.id and e.user_id=e2.user_id 
     and e.status = 12 and e2.status=12 and e.stage=false 
     and e2.stage=false and e.match=true and e2.match=true 
     and e.user_id=12 and e.r_needed=true and e2.r_needed=true
     and e.created_at<e2.created_at 
     and DATE(e.created_at)='2015-10-08'
 );

If this returns the duplicate rows correctly you can switch to delete instead of select *.