X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id F1290475DB3
	for <pgsql-sql@postgresql.org>; Fri, 28 Feb 2003 18:49:55 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 83524-08 for <pgsql-sql@postgresql.org>;
	Fri, 28 Feb 2003 18:49:55 -0500 (EST)
Received: from xeon.unixathome.org (bast.unixathome.org [66.11.174.150])
	by postgresql.org (Postfix) with ESMTP id 7C535476341
	for <pgsql-sql@postgresql.org>; Fri, 28 Feb 2003 16:57:04 -0500 (EST)
Received: by xeon.unixathome.org (Postfix, from userid 1000)
	id D0AF43F4A; Fri, 28 Feb 2003 16:56:56 -0500 (EST)
Received: from localhost (localhost [127.0.0.1])
	by xeon.unixathome.org (Postfix) with ESMTP
	id C40713F49; Fri, 28 Feb 2003 16:56:56 -0500 (EST)
Date: Fri, 28 Feb 2003 16:56:56 -0500 (EST)
From: Dan Langille <dan@langille.org>
X-X-Sender: dan@xeon.unixathome.org
To: Tomasz Myrta <jasiek@klaster.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: DELETE FROM  t WHERE EXISTS
In-Reply-To: <20030228212827.M96860@klaster.net>
Message-ID: <20030228165055.J29368@xeon.unixathome.org>
References: <3E5F530B.1649.424D34C1@localhost>
	<20030228212827.M96860@klaster.net>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/629
X-Sequence-Number: 12027


On Fri, 28 Feb 2003, Tomasz Myrta wrote:

> > Hi folks,
> >
> > I wanted to delete "old" rows from a table.  These are the rows I
> > want to keep:
> >
> > SELECT *
> > FROM clp
> > ORDER BY commit_date
> > LIMIT 100
> >
> > So I tried this:
> >
> > DELETE FROM clp
> > WHERE NOT EXISTS (
> > SELECT *
> > FROM clp
> > ORDER BY commit_date
> > LIMIT 100);
> >
> > Uhh uhh, nothing deleted.  I don't understand why.
> >
> > OK, I can do this instead:
> >
> > DELETE from clp
> > where commit_log_id NOT in (
> > SELECT commit_log_id
> > FROM clp
> > ORDER BY commit_date
> > LIMIT 100);
> >
> > Can you think of a better way?

> delete from clp where commit_date < (select commit_date from clp order
> by commit_date limit 1 offset 100); Regards, Tomasz Myrta

Thank you.  That does it well.  And it also revealed an error in my
original SQL:  a mising "desc".  Here's the plan FYI:


 Seq Scan on clp  (cost=0.00..2.26 rows=34 width=6) (actual
time=8.55..11.92 rows=399 loops=1)
   Filter: (commit_date < $0)
   InitPlan
     ->  Limit  (cost=5.62..5.62 rows=1 width=8) (actual time=7.58..7.60
rows=1 loops=1)
           ->  Sort  (cost=5.37..5.62 rows=101 width=8) (actual
time=6.75..7.17 rows=102 loops=1)
                 Sort Key: commit_date
                 ->  Seq Scan on clp  (cost=0.00..2.01 rows=101 width=8)
(actual time=0.26..3.93 rows=500 loops=1)
 Total runtime: 15.86 msec