How do I delete only a few rows in postgreSQL? I want to fetch 10 rows to delete in a subquery.
My table
Asked
Active
Viewed 5.9k times
2 Answers
59
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);
Rahul Tripathi
- 168,305
- 31
- 280
- 331
-
I only have the same SerialId and valuetimestamp´s. Se my picture that I added. – ArthurDatur Oct 09 '15 at 07:15
-
@ArthurDatur:- You want to remove the duplicates? – Rahul Tripathi Oct 09 '15 at 07:16
-
No, I want to remove half of the posts LIMIT 10 (of 20) bu in the reality it´s thousands of rows There are no duplicates – ArthurDatur Oct 09 '15 at 07:18
-
@ArthurDatur:- Please see my edit. I have update my answer. – Rahul Tripathi Oct 09 '15 at 07:20
-
2I didnt know about CTID! that works fine! Thanks a lot! – ArthurDatur Oct 09 '15 at 07:31
1
If you have some unique identifier (serial, let's call it "id") in your table, then just make something like :
DELETE FROM table WHERE table.id IN (SELECT table.id FROM table WHERE *whatever*)
Add or not something like "LIMIT 0,10"
Julo0sS
- 2,096
- 5
- 28
- 52
-
I only have the same SerialId and valuetimestamp´s. Please se my screendump that I added – ArthurDatur Oct 09 '15 at 07:16