Как извлечь дубликаты записей и удалить их в таблице A, а также вставить эти дубликаты записей в другую таблицу B (в postgres)

Question

Как извлечь дубликаты записей и удалить их в таблице A, а также вставить эти найденные дубликаты записей в другую таблицу B (в postgres db)

SQL-запросы необходимы для моего проекта.

Answer 1

Чтобы удалить дубликаты без уникального столбца, вы можете использовать ctid виртуальный столбец, который по сути то же самое, что и rowid в Oracle:

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

Вы можете использовать returning предложение, чтобы получить удаленные строки и вставить их в другую таблицу:

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;

Если вы хотите увидеть эти удаленные строки, вы можете добавить еще один 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;