sqlpostgresql

How do I delete a fixed number of rows with sorting in 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 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.

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

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


Solution

  • You could try using the ctid:
    demo at db<>fiddle

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

    The ctid is:

    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.

    In case there are tables that inherit from logtable, use DELETE FROM ONLY to prevent this from removing up to 10 rows from each of the descendants that happens to host a row with the same ctid. This field is only unique per table and by default, regular DELETE cascades down the hierarchy.

    If logtable is partitioned, add tableoid to stop this from wiping up to 10 per partition:

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

    DELETE FROM ONLY wouldn't remove anything because the main table is an empty relation, only redirecting to specific partitions.