Удаление дубликата в БД (postgresql), с условияем where из нескольких таблиц

Question

Удаление дубликата в БД (postgresql), с условияем where из нескольких таблиц

Рейтинг: 1Ответов: 2Опубликовано: 15.05.2023

База PG Имеются две таблицы: Таблица INFO , с данными:

Таблица ADDON , связаны между друг другом по столбцу number

Мне надо найти и удалить дубликат в таблице INFO. Дубликат будет считаться записью где одинаковые значения в столбцах number,id,content. При этом в таблице для этого number, поля status и type ='1'.

Найти дублирующую (нужную запись), проблем не возникает.

select i.number,i.id,i.content from INFO i
join ADDON a using(number)
where a.status ='1'
and a.type ='1'
group by i.number,i.id,i.content having count(*) >1

Вывод:

не могу понять как корректо, не удаляя ничего лишнего, удалить вторую дублирующую строчку. При этом первая должна остаться без изменений. По логике тут можно уже добавлять delete from INFO ... но что-то не могу подобрать верное решение. Возможно и селект можно сделать менее топорным.

with dubli as
(
select i.number,i.id,i.content from INFO i
join ADDON a using(number)
where a.status ='1'
and a.type ='1'
group by i.number,i.id,i.content having count(*) >1
--поиск дублей
)
select inf.* from INFO inf 
join dubli using (number)
where inf.number=dubli.number
and inf.id=dubli.id
and inf.content=dubli.content
--что-бы вывел 2 строки, вторую нужно удалить

Для создания аналогичных таблиц

create TABLE INFO 
(number integer,
id integer,
content integer,
flag integer
)

create TABLE ADDON 
(number integer,
status integer,
type integer
)

insert into INFO values
('4111','1','354911','1'),
('4111','1','354911','2'),
('4111','2','354912','3'),
('4112','1','354913','1'),
('4113','1','354914','1')

insert into ADDON values
('4111','1','1'),
('4112','1','1'),
('4113','1','1')

Получилось удалить нужную строку, но какими то костылями...

with dubli as
(
select i.number,i.id,i.content,i.flag from INFO i
join ADDON a using(number)
where a.status ='1'
and a.type ='1'
group by i.number,i.id,i.content having count(*) >1
--поиск дублей
),
alldubli as
(
select inf.* from INFO inf 
join dubli using (number)
where inf.number=dubli.number
and inf.id=dubli.id
and inf.content=dubli.content
--вывод все строк
)
delete from INFO
where number in (select number from alldubli)
and id in (select id from alldubli)
and content in (select content from alldubli)
and flag in (select max(flag) from alldubli)
--удаление нужной

sql postgresql

Источник: Stack Overflow на русском

Answer 1

▲ 1

Основная причина что приходится писать такие вот огромные запросы, она как мне кажется, в том что в таблице INFO нет колонки, которая бы четко определяла о какой именно строке идет речь.

С другой стороны в таблицах PostgreSQL всегда есть "служебное" поле tcid - и если его использовать, то можно сделать что-то навроде

DELETE FROM INFO WHERE ctid = (SELECT ARRAY_AGG(i.ctid) FROM INFO i
JOIN ADDON a USING(number)
WHERE a.status ='1'
AND a.type ='1'
GROUP i.number,i.id,i.content HAVING COUNT(*) >1)[1];

Основной код запроса взят из вопроса. С помощью функции ARRAY_AVG получаем массив значений ctid для записей дублей. После этого удаляем только одну запись из полученного массива.

Сразу надо сказать что поле ctid служебное и его значение может меняться после вакуума или после изменения записей - поэтому строить на нем какую-то логику не стоит. Но как временное решение - может и получится. Но если бы вместо него был идентификатор записи в таблице - все было бы и проще и надежней.

Answer 2

▲ 0

Судя по всему вам нужно что-то вроде этого:

with c as (
  select i.number, i.id, i.content, i.flag, row_number() 
  over(partition by number order by number, id, content, flag) rn
  from info i
  join addon a using(number)
  where
    a.status = '1' and
    a.type = '1'
)
delete from info i
  using c
  where
    i.number = c.number and
    i.id = c.id and
    i.content = c.content and
    i.flag = c.flag and
    c.rn > 1;

Удаление дубликата в БД (postgresql), с условияем where из нескольких таблиц

Ответы