From: | Chris Browne <cbbrowne(at)acm(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: PSQL statement to delete 100 rows meeting certain criteria |
Date: | 2009-12-30 21:20:43 |
Message-ID: | 87ljgk2m6s.fsf@dba2.int.libertyrms.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
shulkae <shulkae(at)gmail(dot)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!"
From | Date | Subject | |
---|---|---|---|
Next Message | Brian Sherwood | 2009-12-31 16:23:26 | loading a file into a field |
Previous Message | Joshua Tolley | 2009-12-30 11:42:55 | Re: DataBase Problem |