Skip site navigation (1) Skip section navigation (2)

Re: Deleting 100 rows which meets certain criteria

From: APseudoUtopia <apseudoutopia(at)gmail(dot)com>
To: shulkae <shulkae(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Deleting 100 rows which meets certain criteria
Date: 2009-12-30 18:43:10
Message-ID: 27ade5280912301043x208bfbf2h986582a99419f3d4@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
On Wed, Dec 30, 2009 at 12:11 PM, 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?
>
> 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.

In response to

Responses

pgsql-general by date

Next:From: Andreas KretschmerDate: 2009-12-30 18:48:27
Subject: Re: Deleting 100 rows which meets certain criteria
Previous:From: Gastón QuirogaDate: 2009-12-30 18:07:01
Subject: Re: pg_dump ERROR, usename "postgres" duplicated

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group