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!
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'sctid
will change if it is updated or moved byVACUUM FULL
. Thereforectid
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.