How to retrieve duplicate records and delete them in table A, also insert these duplicate records in another table B (in postgres)

Question

how to retrieve duplicate records and delete them in table A, also insert these retrieved duplicate records in another table B (in postgres db)

SQL query's are required for my project.

Answer 1

To delete duplicates without having a unique column you can use the ctid virtual column which is essentially the same thing as the rowid in Oracle:

delete from table_A t1
where ctid <> (select min(t2.ctid)
             from table_A t2
             where t1.unique_column = t2.unique_column);

You can use the returning clause to get the deleted rows and insert them into the other table:

with deleted as (
  delete from table_A x1
  where ctid <> (select min(t2.ctid)
                 from table_A t2
                 where t1.unique_column = t2.unique_column);

  returning * 
)
insert into table_B (col_1, col_2)
select unique_column, some_other_column
from deleted;

If you further want to see those deleted rows, you can throw in another CTE:

with deleted as (
  delete from table_A x1
  where ctid <> (select min(t2.ctid)
                 from table_A t2
                 where t1.unique_column = t2.unique_column);

  returning * 
), moved as (
  insert into table_B (col_1, col_2)
  select unique_column, some_other_column
  from deleted
  returning *
)
select *
from moved;