delete some old records from postgres table

Question

I have a table as:

table t1
id int
room text
record_date timestamp

How can I delete some records in t1 table, except the newest 100 records of a room(record_date is used to save the timestamp to compare with)?

With mysql I did the next and it works:

DELETE  p FROM t1 p
            LEFT JOIN 
            (SELECT id,room
            FROM t1
            ORDER BY record_date DESC
            LIMIT 101) p2 on p2.id=p.id and p2.room=p.room 
            WHERE p2.id IS NULL and p.room='myroom'

But postgres doesn´t works with it and results on error.

score 1 · Answer 1 · answered Jul 16 '23 at 16:49

1

Try the following :

DELETE FROM t1
WHERE (room, id) NOT IN (
    SELECT room, id
    FROM t1
    WHERE room = 'myroom'
    ORDER BY record_date DESC
    LIMIT 100
);

answered Jul 16 '23 at 16:49

Amira Bedhiafi

8,088
6
24
60

score 0 · Answer 2 · answered Jul 16 '23 at 17:18

0

Try this adapted from this answer:

WITH todelete AS (
   SELECT * FROM t1
   WHERE room = 'myroom'
   ORDER BY record_date DESC
   OFFSET 100 ROWS
 )
 DELETE FROM todelete;

answered Jul 16 '23 at 17:18

Lavie

136
7

score 0 · Answer 3 · answered Jul 16 '23 at 19:02

You could try the following command:

DELETE FROM t1
WHERE room = 'myroom' AND NOT EXISTS 
(
   SELECT id
   FROM t1 p2
   WHERE p2.room = t1.room
   ORDER BY record_date DESC
   OFFSET 100
   LIMIT 1
);

The reason of using 'NOT EXISTS' will ensure that selected records are older than the 100th newest record in the room.

delete some old records from postgres table

3 Answers3