Skip site navigation (1) Skip section navigation (2)

Re: DELETE FROM t WHERE EXISTS

From: Dan Langille <dan(at)langille(dot)org>
To: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: DELETE FROM t WHERE EXISTS
Date: 2003-02-28 22:01:11
Message-ID: 20030228170014.O29368@xeon.unixathome.org (view raw or flat)
Thread:
Lists: pgsql-sql
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
>
>
>
>

In response to

pgsql-sql by date

Next:From: Dmitry TkachDate: 2003-02-28 22:42:00
Subject: Re: disable constraints
Previous:From: Dan LangilleDate: 2003-02-28 21:58:34
Subject: Re: DELETE FROM t WHERE EXISTS

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group