Re: Deleting 100 rows which meets certain criteria

[Andreas Kretschmer <akretschmer(at)spamfence(dot)net>]

shulkae <shulkae(at)gmail(dot)com> wrote:

> I am newbie to postgres/SQL.
> 
> I want to delete all rows exceeding 400 hours (10 days) and I want to
> limit deletion of only 100 records at a time.
> 
> I was trying the following in PostgreSQL:
> 
> DELETE from mytable WHERE (now() - timestamp_field  > INTERVAL '400
> hour' ) LIMIT 100;
> 
> Looks like DELETE syntax doesn't support LIMIT.
> 
> Is there any other way to achieve this?

Select all rows with this condition, order by this field descending, limit 100.

Now you have all possible rows to delete. Simple example:

test=*# select * from shulkae ;
 i
----
  2
  4
  1
  6
  4
  1
  9
 11
 13
  2
 17
 15
(12 rows)

Time: 0.211 ms
test=*# delete from shulkae where i in (select * from shulkae where i > 10 order by i desc limit 2);
DELETE 2
Time: 0.542 ms
test=*# select * from shulkae ;
 i
----
  2
  4
  1
  6
  4
  1
  9
 11
 13
  2
(10 rows)

Time: 0.200 ms
test=*#



Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°