Question 209150 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – delete some old records from postgres table

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.

Tags:

3

Answers


  1. 0

    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
);
    Login or Signup to reply.
  2. 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;
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search