PostgreSQL DELETE FROM (SELECT * FROM table FETCH FIRST 10 ROWS ONLY)

Question

How do I delete only a few rows in postgreSQL? I want to fetch 10 rows to delete in a subquery.

My table

Answer 1

You need to use a where condition as per your requirement like this:

delete from mytable where id in(1,2,3,4,5,6,7,8,9,10)

or

delete from mytable where id in(select id from mytable where someconditon)

or you can try like this if you want to delete top 10 using ctid:

DELETE FROM mytable 
WHERE ctid IN (
    SELECT ctid
    FROM mytable 
    GROUP BY s.serialId, s.valuetimestamp
    ORDER BY s.serialId
    LIMIT 10
)

If you are looking to remove the duplicates from your table then try this:

DELETE FROM mytable
 WHERE ctid NOT IN
  (SELECT MAX(s.ctid)
    FROM table s
    GROUP BY s.serialId, s.valuetimestamp);