How to Delete a Fixed Number of Rows with Sorting in Postgresql

How do I delete a fixed number of rows with sorting in PostgreSQL?

You could try using the ctid:

DELETE FROM 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.

There's also oid but that only exists if you specifically ask for it when you create the table.

Delete with sorting in PostgreSQL

The only way I can think of is delete in a loop.
Here's an example using a cursor:

DO $$
DECLARE 
    test1_row test1%ROWTYPE;
    DECLARE cur CURSOR FOR SELECT * FROM test1 ORDER BY co DESC FOR UPDATE;

BEGIN
    FOR test1_row IN cur 
    LOOP
        DELETE FROM test1 WHERE CURRENT OF cur;
    END LOOP;
END $$

An example without a cursor:

DO $$
DECLARE 
    test1_row test1%ROWTYPE;    
BEGIN
    FOR test1_row IN SELECT * FROM test1 ORDER BY co DESC
    LOOP
        DELETE FROM test1 WHERE th = test1_row.th AND co = test1_row.co AND ty = test1_row.ty;
    END LOOP;
END $$

Note: the WHERE condition in DELETE statement above is not reliable because test1 table does not have a primary/unique key. In other cases, you could just use: WHERE id = row.id

How to delete first few records from a table without any criteria in PostgreSQL?

Try to prepare subquery with LIMIT as below

DELETE FROM txn_log
WHERE txn_log_pgm_id IN (SELECT txn_log_pgm_id
                         FROM txn_log
                         ORDER BY txn_log_timestamp asc
                         LIMIT 500)

Delete ranges of id's

You could do it this way:

DELETE
FROM users
WHERE 
   id between 754 and 759
OR id between 765 and 799

---

Related Topics