Re: Deleting 100 rows which meets certain criteria

shulkae wrote:
> DELETE from mytable WHERE (now() - timestamp_field > INTERVAL '400
> hour' ) LIMIT 100;
>

Force of habit (not sure if the optimizer does this trick for you) is
first to rewrite this as follows:

DELETE from mytable WHERE timestamp_field < (now() - INTERVAL '400
hour' ) LIMIT 100;

Just to turn the comparison into a constant being compared with the field.

If there's a useful primary key on this table, you can do this to delete:

DELETE FROM mytable where pkey IN (SELECT pkey from mytable WHERE
timestamp_field < (now() - INTERVAL '400 hour' ) LIMIT 100);

If there's not a primary key, you can use a hidden field named ctid to
get your record list:

http://www.postgresql.org/docs/current/static/ddl-system-columns.html

And then use that as the way to communicate the candidate deletion list
out of the subselect:

DELETE FROM mytable where ctid IN (SELECT ctid from mytable WHERE
timestamp_field < (now() - INTERVAL '400 hour' ) LIMIT 100);

The main advantage of using the primary key is that the result will be
more portable to other databases--the ctid field is very much a
PostgreSQL specific hack.

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com