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

Preserving Row Deletion Count in PostgreSQL Without Sorting and a Primary Key

In PostgreSQL, the inability to combine ordering or limit with delete syntax poses a challenge when attempting to delete a specific number of rows while preserving the order in which they are selected. This is further complicated by the absence of a primary key in the table.

Solution: Utilizing the ctid

To circumvent these limitations, we can leverage the ctid (Current Tuple Identifier) of the rows:

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

The ctid provides a unique identifier for each row version within its table. By selecting the ctids of the first 10 rows sorted by timestamp and using them in the DELETE statement, we can effectively remove the desired number of rows, even if all timestamp values are the same.

Consider Partitioned Tables

If the logtable is partitioned, we need to account for the possibility of multiple partitions containing rows with the same timestamp. To prevent deleting too many rows, we can include the tableoid (table identifier) in the query:

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

This ensures that the deletion is limited to the specified number of rows within each partition.

By utilizing the ctid and tableoid, we can implement the desired functionality of deleting a fixed number of rows with sorting in PostgreSQL, even in the absence of a primary key.

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