Как удалить фиксированное количество строк с сортировкой в PostgreSQL?

Я пытаюсь перенести некоторые старые MySQL-запросы в PostgreSQL, но у меня возникают проблемы с этим:

DELETE FROM logtable ORDER BY timestamp LIMIT 10;

PostgreSQL не разрешает упорядочение или ограничения в синтаксисе удаления, а таблица не имеет первичного ключа, поэтому я не могу использовать подзапрос. Кроме того, я хочу сохранить поведение, в котором запрос удаляет точно указанное число или записи - например, если таблица содержит 30 строк, но все они имеют одну и ту же метку времени, я все равно хочу удалить 10, хотя это не имеет значения который 10.

Итак, как удалить фиксированное количество строк с сортировкой в PostgreSQL?

Изменить: никакой первичный ключ не означает столбец log_id или аналогичный. Ах, радости унаследованных систем!

Ответы

Ответ 1

Вы можете попробовать использовать ctid:

DELETE FROM logtable
WHERE ctid IN (
    SELECT ctid
    FROM logtable
    ORDER BY timestamp
    LIMIT 10
)

ctid:

Физическое расположение версии строки в ее таблице. Обратите внимание, что хотя ctid можно использовать для быстрого поиска версии строки, строка ctid изменится, если она будет обновлена или перемещена на VACUUM FULL. Поэтому ctid бесполезен как идентификатор длинной строки.

Там также oid, но это существует только в том случае, если вы специально запрашиваете его при создании таблицы.

Ответ 2

Postgres docs рекомендуют использовать массив вместо IN и подзапроса. Это должно работать намного быстрее

DELETE FROM logtable 
WHERE id = any (array(SELECT id FROM logtable ORDER BY timestamp LIMIT 10));

Этот и некоторые другие трюки можно найти здесь

Ответ 3

delete from logtable where log_id in (
    select log_id from logtable order by timestamp limit 10);

Ответ 4

Предполагая, что вы хотите удалить ЛЮБЫЕ 10 записей (без заказа), вы можете сделать это:

DELETE FROM logtable as t1 WHERE t1.ctid < (select t2.ctid from logtable as t2  where (Select count(*) from logtable t3  where t3.ctid < t2.ctid ) = 10 LIMIT 1);

В моем случае использования, удалив 10M записей, это оказалось быстрее.

Ответ 5

Вы можете написать процедуру, которая пересекает удаление для отдельных строк, процедура может принимать параметр, чтобы указать количество элементов, которые вы хотите удалить. Но это немного избыточно по сравнению с MySQL.