pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: DELETE FROM t WHERE EXISTS
11+ messages / 8 participants
[nested] [flat]
* DELETE FROM t WHERE EXISTS
@ 2003-02-28 17:16 Dan Langille <dan@langille.org>
2003-02-28 13:28 ` Re: DELETE FROM t WHERE EXISTS Tomasz Myrta <jasiek@klaster.net>
2003-02-28 19:25 ` Re: DELETE FROM t WHERE EXISTS Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
2003-02-28 19:30 ` Re: DELETE FROM t WHERE EXISTS Jeff Eckermann <jeff_eckermann@yahoo.com>
2003-02-28 20:11 ` Re: DELETE FROM t WHERE EXISTS Tom Lane <tgl@sss.pgh.pa.us>
2003-02-28 20:34 ` Re: DELETE FROM t WHERE EXISTS Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-02-28 20:36 ` Re: DELETE FROM t WHERE EXISTS Robert Treat <xzilla@users.sourceforge.net>
2003-02-28 22:47 ` Re: DELETE FROM t WHERE EXISTS Dmitry Tkach <dmitry@openratings.com>
0 siblings, 7 replies; 11+ messages in thread
From: Dan Langille @ 2003-02-28 17:16 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: DELETE FROM t WHERE EXISTS
2003-02-28 17:16 DELETE FROM t WHERE EXISTS Dan Langille <dan@langille.org>
@ 2003-02-28 13:28 ` Tomasz Myrta <jasiek@klaster.net>
2003-02-28 21:56 ` Re: DELETE FROM t WHERE EXISTS Dan Langille <dan@langille.org>
6 siblings, 1 reply; 11+ messages in thread
From: Tomasz Myrta @ 2003-02-28 13:28 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: DELETE FROM t WHERE EXISTS
2003-02-28 17:16 DELETE FROM t WHERE EXISTS Dan Langille <dan@langille.org>
2003-02-28 13:28 ` Re: DELETE FROM t WHERE EXISTS Tomasz Myrta <jasiek@klaster.net>
@ 2003-02-28 21:56 ` Dan Langille <dan@langille.org>
0 siblings, 0 replies; 11+ messages in thread
From: Dan Langille @ 2003-02-28 21:56 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: DELETE FROM t WHERE EXISTS
2003-02-28 17:16 DELETE FROM t WHERE EXISTS Dan Langille <dan@langille.org>
@ 2003-02-28 19:25 ` Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
6 siblings, 0 replies; 11+ messages in thread
From: Jakub Ouhrabka @ 2003-02-28 19:25 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: pgsql-sql
Hi,
> 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.
Because for each row in clp is true that the subselect is returning some
rows... The subselect is independant on the outer select as you wrote
it...
> Can you think of a better way?
Mark the rows you want to delete first (add a column or use a temp table)
and then delete the marked rows, e.g.:
create temp table tmp (commit_log_id int, del bool);
insert into tmp (commit_log_id, del) select commit_log_id, true from clp;
update tmp set del = false from (select commit_log_id from clp order by
commit_date limit 100) as del where del.commit_log_id = tmp.commit_log_id;
delete from clp where clp.commit_log_id = tmp.commit_log_id and tmp.del =
true;
or with the extra column:
update clp set del = true;
update clp set del = false from (select commit_log_id from clp order by
commit_date limit 100) as del where del.commit_log_id = clp.commit_log_id;
delete from clp where del = true;
hth, kuba
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: DELETE FROM t WHERE EXISTS
2003-02-28 17:16 DELETE FROM t WHERE EXISTS Dan Langille <dan@langille.org>
@ 2003-02-28 19:30 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
6 siblings, 0 replies; 11+ messages in thread
From: Jeff Eckermann @ 2003-02-28 19:30 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; pgsql-sql
--- Dan Langille <dan@langille.org> 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.
>
Your WHERE clause will never evaluate to true in this
case, because something will always be returned by the
subselect.
> 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?
> --
AFAIK joins cannot be used with DELETEs (but see
below), so you are stuck with a subselect. If you
don't like the performance of the IN, you could do an
EXISTS using a correlated subselect from a subselect,
but that is ugly. Or you could do it in two steps:
SELECT INTO TEMP sometable *
FROM clp
ORDER BY commit_date
LIMIT 100;
DELETE FROM clp
WHERE commit_date = sometable.commit_date;
That last must be converted into a join clause
somehow, but right now I am too lazy to turn on
logging to find out what :-)
__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: DELETE FROM t WHERE EXISTS
2003-02-28 17:16 DELETE FROM t WHERE EXISTS Dan Langille <dan@langille.org>
@ 2003-02-28 20:11 ` Tom Lane <tgl@sss.pgh.pa.us>
6 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2003-02-28 20:11 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: pgsql-sql
"Dan Langille" <dan@langille.org> writes:
> 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.
Because the inner SELECT is a constant: you forgot to make it depend on
the current outer row. So EXISTS succeeds at every row, unless clp is
already empty.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: DELETE FROM t WHERE EXISTS
2003-02-28 17:16 DELETE FROM t WHERE EXISTS Dan Langille <dan@langille.org>
@ 2003-02-28 20:34 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-02-28 21:58 ` Re: DELETE FROM t WHERE EXISTS Dan Langille <dan@langille.org>
6 siblings, 1 reply; 11+ messages in thread
From: Stephan Szabo @ 2003-02-28 20:34 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: pgsql-sql
On Fri, 28 Feb 2003, 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.
As long as the inner select returns at least 1 result NOT EXISTS is
going to return false (you haven't correlated the two queries at all).
> 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?
Possibly something like:
DELETE FROM clp
WHERE NOT EXISTS (
select * from (select * from clp order by commit_date limit 100) tmp
where tmp.commit_log_id = clp.commit_log_id
);
But I haven't tried it for stupid errors, and am not sure that it'd end up
being any better than NOT IN anyway.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: DELETE FROM t WHERE EXISTS
2003-02-28 17:16 DELETE FROM t WHERE EXISTS Dan Langille <dan@langille.org>
2003-02-28 20:34 ` Re: DELETE FROM t WHERE EXISTS Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2003-02-28 21:58 ` Dan Langille <dan@langille.org>
0 siblings, 0 replies; 11+ messages in thread
From: Dan Langille @ 2003-02-28 21:58 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
On Fri, 28 Feb 2003, Stephan Szabo wrote:
>
> On Fri, 28 Feb 2003, 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.
>
> As long as the inner select returns at least 1 result NOT EXISTS is
> going to return false (you haven't correlated the two queries at all).
>
> > 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?
>
> Possibly something like:
> DELETE FROM clp
> WHERE NOT EXISTS (
> select * from (select * from clp order by commit_date limit 100) tmp
> where tmp.commit_log_id = clp.commit_log_id
> );
>
> But I haven't tried it for stupid errors, and am not sure that it'd end up
> being any better than NOT IN anyway.
Thank you. Here's that plan:
Seq Scan on clp (cost=0.00..544.87 rows=50 width=6) (actual
time=93.71..763.85 rows=400 loops=1)
Filter: (NOT (subplan))
SubPlan
-> Subquery Scan tmp (cost=5.37..5.62 rows=100 width=12) (actual
time=1.51..1.51 rows=0 loops=500)
Filter: (commit_log_id = $0)
-> Limit (cost=5.37..5.62 rows=100 width=12) (actual
time=0.03..1.09 rows=90 loops=500)
-> Sort (cost=5.37..5.62 rows=101 width=12) (actual
time=0.02..0.38 rows=91 loops=500)
Sort Key: commit_date
-> Seq Scan on clp (cost=0.00..2.01 rows=101
width=12) (actual time=0.23..3.88 rows=500 loops=1)
Total runtime: 768.14 msec
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: DELETE FROM t WHERE EXISTS
2003-02-28 17:16 DELETE FROM t WHERE EXISTS Dan Langille <dan@langille.org>
@ 2003-02-28 20:36 ` Robert Treat <xzilla@users.sourceforge.net>
2003-02-28 22:01 ` Re: DELETE FROM t WHERE EXISTS Dan Langille <dan@langille.org>
6 siblings, 1 reply; 11+ messages in thread
From: Robert Treat @ 2003-02-28 20:36 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: DELETE FROM t WHERE EXISTS
2003-02-28 17:16 DELETE FROM t WHERE EXISTS Dan Langille <dan@langille.org>
2003-02-28 20:36 ` Re: DELETE FROM t WHERE EXISTS Robert Treat <xzilla@users.sourceforge.net>
@ 2003-02-28 22:01 ` Dan Langille <dan@langille.org>
0 siblings, 0 replies; 11+ messages in thread
From: Dan Langille @ 2003-02-28 22:01 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: 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
>
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: DELETE FROM t WHERE EXISTS
2003-02-28 17:16 DELETE FROM t WHERE EXISTS Dan Langille <dan@langille.org>
@ 2003-02-28 22:47 ` Dmitry Tkach <dmitry@openratings.com>
6 siblings, 0 replies; 11+ messages in thread
From: Dmitry Tkach @ 2003-02-28 22:47 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>
What about
select * into temp rows_to_keep from clp order by commit_date limit 100;
truncate clp;
insert into clp select * from rows_to_keep;
Dima.
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?
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2003-02-28 22:47 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-28 17:16 DELETE FROM t WHERE EXISTS Dan Langille <dan@langille.org>
2003-02-28 13:28 ` Tomasz Myrta <jasiek@klaster.net>
2003-02-28 21:56 ` Dan Langille <dan@langille.org>
2003-02-28 19:25 ` Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
2003-02-28 19:30 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2003-02-28 20:11 ` Re: DELETE FROM t WHERE EXISTS Tom Lane <tgl@sss.pgh.pa.us>
2003-02-28 20:34 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-02-28 21:58 ` Dan Langille <dan@langille.org>
2003-02-28 20:36 ` Robert Treat <xzilla@users.sourceforge.net>
2003-02-28 22:01 ` Dan Langille <dan@langille.org>
2003-02-28 22:47 ` Dmitry Tkach <dmitry@openratings.com>
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