如何在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!

推荐答案

您可以尝试使用

You could try using the ctid:

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

ctid是:

表中行版本的物理位置.请注意，尽管ctid可用于非常快速地定位行版本，但是如果VACUUM FULL更新或移动了行，则ctid将会更改.因此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屋！