如何在 PostgreSQL 中通过排序删除固定数量的行？

我正在尝试将一些旧的 MySQL 查询移植到 PostgreSQL，但是我遇到了这个问题:

DELETE FROM logtable ORDER BY timestamp LIMIT 10;

PostgreSQL 不允许在其删除语法中进行排序或限制，而且表没有主键，所以我不能使用子查询。此外，我想保留查询删除给定数字或记录的没错的行为——例如，如果表包含30行，但它们都有相同的时间戳，我仍然想删除10，尽管哪10并不重要。

那么，如何在 PostgreSQL 中通过排序删除固定数量的行呢？

编辑: 没有主键意味着没有 log_id列或类似的列。啊，遗留系统的乐趣！

148089 次浏览

小开

您可以编写一个过程，循环遍历各行的删除操作，该过程可以采用一个参数来指定要删除的项目数。但与 MySQL 相比，这有点夸张了。

小开

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

小开

Postgres 文档建议使用数组而不是 IN 和子查询。这样应该会快得多

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

这和其他一些技巧可以找到给你

小开

最佳答案

你可以试试使用 ctid:

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

ctid是:

行版本在其表中的物理位置。请注意，虽然 ctid可以用来快速定位行版本，但是如果 VACUUM FULL更新或移动行，则行的 ctid将发生变化。因此，ctid作为长期行标识符是无用的。

还有 oid，但是只有在您创建表时特别请求它时才存在。

小开

假设您想删除任意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 条记录会更快。

小开

如果没有主键，可以使用数组 Where IN 语法和复合键。

delete from table1 where (schema,id,lac,cid) in (select schema,id,lac,cid from table1 where lac = 0 limit 1000);

这招对我很管用。