X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C1D2B475E4A for ; Fri, 28 Feb 2003 18:47:05 -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-01 for ; Fri, 28 Feb 2003 18:47:01 -0500 (EST) Received: from xeon.unixathome.org (bast.unixathome.org [66.11.174.150]) by postgresql.org (Postfix) with ESMTP id EC9DF476349 for ; Fri, 28 Feb 2003 17:01:18 -0500 (EST) Received: by xeon.unixathome.org (Postfix, from userid 1000) id E4D013F4A; Fri, 28 Feb 2003 17:01:11 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by xeon.unixathome.org (Postfix) with ESMTP id DD9593F49; Fri, 28 Feb 2003 17:01:11 -0500 (EST) Date: Fri, 28 Feb 2003 17:01:11 -0500 (EST) From: Dan Langille X-X-Sender: dan@xeon.unixathome.org To: Robert Treat Cc: pgsql-sql@postgresql.org Subject: Re: DELETE FROM t WHERE EXISTS In-Reply-To: <1046464564.2038.28.camel@camel> Message-ID: <20030228170014.O29368@xeon.unixathome.org> References: <3E5F530B.1649.424D34C1@localhost> <1046464564.2038.28.camel@camel> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/627 X-Sequence-Number: 12025 Ahh yes, thanks for finding that off-by-one situation. Your query is nearly identical to one from Tomasz Myrta. Seq Scan on clp (cost=0.00..2.26 rows=34 width=6) (actual time=7.98..11.42 rows=400 loops=1) Filter: (commit_date < $0) InitPlan -> Limit (cost=5.62..5.62 rows=1 width=8) (actual time=7.29..7.31 rows=1 loops=1) -> Sort (cost=5.37..5.62 rows=101 width=8) (actual time=6.49..6.90 rows=101 loops=1) Sort Key: commit_date -> Seq Scan on clp (cost=0.00..2.01 rows=101 width=8) (actual time=0.21..3.73 rows=500 loops=1) Total runtime: 15.36 msec (8 rows) On Fri, 28 Feb 2003, Robert Treat wrote: > delete from clp where commit_date < (select commit_date from clp order > by commit_date offset 99 limit 1); > > Robert Treat > > On Fri, 2003-02-28 at 12:16, Dan Langille 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? > > -- > > Dan Langille : http://www.langille.org/ > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > >