如何在 PostgreSQL 中通过排序删除固定数量的行? [英] How do I delete a fixed number of rows with sorting in PostgreSQL?

问题描述

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

I'm trying to port some old MySQL queries to PostgreSQL, but I'm having trouble with this one:

DELETE FROM logtable ORDER BY timestamp LIMIT 10;

PostgreSQL 不允许在其删除语法中进行排序或限制，并且该表没有主键，因此我无法使用子查询.此外，我想保留查询删除完全给定数字或记录的行为——例如，如果表包含 30 行但它们都有相同的时间戳，我仍然想删除 10，虽然哪个 10 无关紧要.

PostgreSQL doesn't allow ordering or limits in its delete syntax, and the table doesn't have a primary key so I can't use a subquery. Additionally, I want to preserve the behavior where the query deletes exactly the given number or records -- for example, if the table contains 30 rows but they all have the same timestamp, I still want to delete 10, although it doesn't matter which 10.

所以；如何在 PostgreSQL 中通过排序删除固定数量的行?

So; how do I delete a fixed number of rows with sorting in PostgreSQL?

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

No primary key means there's no log_id column or similar. Ah, the joys of legacy systems!

推荐答案

您可以尝试使用 ctid:

You could try using the ctid:

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

ctid 是:

行版本在其表中的物理位置.请注意，虽然 ctid 可用于非常快速地定位行版本，但如果行的 ctid 被 VACUUM FULL 更新或移动，它将会改变>.因此 ctid 作为长期行标识符是没有用的.

The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change if it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier.

还有 oid，但只有在创建表时明确要求时才会存在.

There's also oid but that only exists if you specifically ask for it when you create the table.

这篇关于如何在 PostgreSQL 中通过排序删除固定数量的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！