How to Delete a Specific Number of Sorted Rows in PostgreSQL Without a Primary Key?

Deleting a Fixed Number of Rows with Sorting in PostgreSQL

Deleting rows with sorting can be a challenge in PostgreSQL due to its limitations in the DELETE syntax. This issue is further compounded by the absence of a primary key, making subquery solutions impractical.

To address this challenge, you can utilize the ctid field, a unique identifier for each row within a table. By leveraging the ctid, you can construct a query that targets specific rows for deletion based on sorting criteria:

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

The ctid field provides a unique identifier for each row version. However, it's essential to note that it may change if the row is updated or moved by VACUUM FULL. Additionally, if the logtable has inherited tables, the DELETE FROM ONLY clause prevents accidental deletion of rows from descendant tables.

In case the logtable is partitioned, it's necessary to include tableoid in the query to ensure deletion occurs only across the intended partitions:

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

Using these techniques, you can effectively delete a fixed number of rows based on sorting criteria, while preserving the behavior of exact deletion, ensuring that the specified number of rows is removed regardless of duplicate timestamps.

The above is the detailed content of How to Delete a Specific Number of Sorted Rows in PostgreSQL Without a Primary Key?. For more information, please follow other related articles on the PHP Chinese website!