Re: Deleting 100 rows which meets certain criteria

[Greg Smith <greg(at)2ndquadrant(dot)com>]

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