Postgres - remove duplicate records

Question

I have write a query to remove duplicate records from a table.

DELETE e FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY emp_id) AS rn
    FROM employee1
) e 
WHERE e.rn > 1

But i got this error

ERROR: syntax error at or near "e" LINE 1: DELETE e FROM.

Can someone help me. Thank you!

Answer 1

If you have a unique id, then use that. But, if you don't, you can use ctid:

delete from employee1 e
    where e.ctid > (select min(e.ctid) from employee1 e2 where e2.emp_id = e.emp_id);