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

Question

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

My table

Answer 1

You need to use the where clause to suit your requirements:

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 this if you want to remove the 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 want to remove duplicates from your table, try the following:

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

Answer 2

If you have a unique identifier (serial, let him call it "id") in your table, then just do something like:

 DELETE FROM table WHERE table.id IN (SELECT table.id FROM table WHERE *whatever*) 

Add or not something like "LIMIT 0.10"