How do I delete only a few rows in postgreSQL? I want to fetch 10 rows to delete in a subquery.
My table
Add a comment
|
2 Answers
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);
5 Comments
ArthurDatur
I only have the same SerialId and valuetimestamp´s. Se my picture that I added.
Rahul Tripathi
@ArthurDatur:- You want to remove the duplicates?
ArthurDatur
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
Rahul Tripathi
@ArthurDatur:- Please see my edit. I have update my answer.
ArthurDatur
I didnt know about CTID! that works fine! Thanks a lot!
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"
1 Comment
ArthurDatur
I only have the same SerialId and valuetimestamp´s. Please se my screendump that I added