From: Dan Langille <dan@langille.org>
To: Tomasz Myrta <jasiek@klaster.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: DELETE FROM t WHERE EXISTS
Date: Fri, 28 Feb 2003 16:56:56 -0500 (EST)
Message-ID: <20030228165055.J29368@xeon.unixathome.org> (raw)
In-Reply-To: <20030228212827.M96860@klaster.net>
References: <3E5F530B.1649.424D34C1@localhost>
<20030228212827.M96860@klaster.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dan@langille.org, jasiek@klaster.net
Subject: Re: DELETE FROM t WHERE EXISTS
In-Reply-To: <20030228165055.J29368@xeon.unixathome.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox