From: Chris Browne <cbbrowne@acm.org>
Subject: Re: PSQL statement to delete 100 rows meeting certain criteria
Date: Wed, 30 Dec 2009 16:20:43 -0500
Organization: cbbrowne Computing Inc
Lines: 37
Message-ID: <87ljgk2m6s.fsf@dba2.int.libertyrms.com>
References: <7e2a8d5e-f7a7-4b7a-b35b-e361544e4b4a@p8g2000yqb.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.110009 (No Gnus v0.9) XEmacs/21.4.22 (linux)
Cancel-Lock: sha1:Ma+ZnzSdUbmcCSCdwS1hnJz1b1o=
To: pgsql-sql@postgresql.org

shulkae <shulkae@gmail.com> writes:
> 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?

Sure...

Supposing mytable has, as a unique key, column "id"...

  delete from mytable where
   id in (select id from mytable 
          where timestamp_field < now() - '400 hours'::interval 
          limit 100);

I once set up a process where I did this exact sort of thing, complete
with a "back-off" scheme where each iteration would check a sequence to
see if a lot of new tuples had come in since last time, and:

  a) If the system was looking busy, it would only delete a small
     bit of data;
  b) If the system was not busy at all, it would delete quite a bit
     more obsolete data.
-- 
output = reverse("ofni.secnanifxunil" "@" "enworbbc")
Signs of a Klingon Programmer #1: "Our users will  know fear and cower
before our software. Ship it! Ship it and let  them flee like the dogs
they are!"