shulkae <shulkae@xxxxxxxxx> 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° -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
