Re: Deleting 100 rows which meets certain criteria - Mailing list pgsql-general

From APseudoUtopia
Subject Re: Deleting 100 rows which meets certain criteria
Date
Msg-id 27ade5280912301043x208bfbf2h986582a99419f3d4@mail.gmail.com
Whole thread Raw
In response to Deleting 100 rows which meets certain criteria  (shulkae <shulkae@gmail.com>)
Responses Re: Deleting 100 rows which meets certain criteria  (Anthony <osm@inbox.org>)
List pgsql-general
On Wed, Dec 30, 2009 at 12:11 PM, shulkae <shulkae@gmail.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?
>
> thanks
> Shul
>

You're correct to infer that DELETE does not support LIMIT clauses.
The reason for this is there is no way to tell exactly which rows will
be deleted (unless ORDER BY is used - but there are still other issues
with that as well).

You could, however, do something like such:
DELETE FROM "table" WHERE "column" IN (SELECT "column" FROM "table"
LIMIT 100 OFFSET 0);

(But again, you would need explicit ORDER BY clauses to determine
exactly which rows are actually deleted.

pgsql-general by date:

Previous
From: Nick
Date:
Subject: postgresql/postgis installation
Next
From: Andreas Kretschmer
Date:
Subject: Re: Deleting 100 rows which meets certain criteria