Remove duplicate rows in Postgres

Though the question is already answered I am adding two different answers here using cte.

 create table Employee(ID int, Name varchar(50), Surname varchar(50)); insert into Employee values(143, 'Amy', 'Flowers'); insert into Employee values(245, 'Natasha', 'Smith'); insert into Employee values(365, 'John', 'Alexander'); insert into Employee values(445, 'Natasha', 'Smith'); insert into Employee values(565, 'Monica', 'Withhouse'); insert into Employee values(644, 'Amy', 'Flowers'); insert into Employee values(1023, 'Amy', 'Alexander'); create table employee_details ( ID int, Employee_id int, Document_numer varchar(50)); insert into employee_details values(1, 644, 'XXXXXXXXX'); insert into employee_details values(2, 245, 'XXXXXX'); insert into employee_details values(3, 365, 'XXXXXX'); 

Delete query 1:

 with duplicate_employees as ( select * , count(id)over(partition by name,surname) duplicate_count from Employee ) delete from Employee where id in( select id from duplicate_employees de where duplicate_count >1 and not exists ( select 1 from employee_details e where e.Employee_id = de.ID ) ) select * from employee 

Output:

db<>fiddle here

Delete query 2:

 with cte as ( Select *, count(*)over(partition by name,surname) duplicate_count, (case when exists ( select 1 from employee_details ed where ed.Employee_id = e.ID ) then 1 else 0 end) exist_in_details from Employee e ) delete from Employee where id in (select id from cte where duplicate_count>1 and exist_in_details=0 ) select * from Employee 

Output:

db<>fiddle here