Thread: using limit with delete
Hi all, I'm trying to use a limit clause with delete, but it doesn't work at the moment (are there plans to add this - I could try to do up a patch ?). eg. delete from table where x='1' limit 1000; (so truncate is out - I have a 'where' clause). Is there another way to approach this? I'm trying to delete records through a webapp and if there are 500,000 records for example, I can't really leave the page open and expect it to finish... -- Regards, Chris Smith Unit 2, 3 National Street, Rozelle, NSW 2039 Australia Ph: +61 2 9555 5570 Fx: +61 2 9555 5571 email: info@interspire.com web: http://www.interspire.com
Chris Smith wrote:
> I'm trying to use a limit clause with delete, but it doesn't work at the
> moment
It isn't in the SQL standard, and it would have undefined behavior: the
sort order of a result set without ORDER BY is unspecified, so you would
have no way to predict which rows DELETE would remove.
> delete from table where x='1' limit 1000;
You could use a subquery to achieve this:
DELETE FROM table WHERE x IN
(SELECT x FROM table ... ORDER BY ... LIMIT ...);
-Neil
I don't care about the order in my particular case, just that I have to clear the table. I'll try the subquery and see how I go :) Thanks! Neil Conway wrote: > Chris Smith wrote: > >> I'm trying to use a limit clause with delete, but it doesn't work at >> the moment > > > It isn't in the SQL standard, and it would have undefined behavior: the > sort order of a result set without ORDER BY is unspecified, so you would > have no way to predict which rows DELETE would remove. > >> delete from table where x='1' limit 1000; > > > You could use a subquery to achieve this: > > DELETE FROM table WHERE x IN > (SELECT x FROM table ... ORDER BY ... LIMIT ...); > > -Neil > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Regards, Chris Smith Unit 2, 3 National Street, Rozelle, NSW 2039 Australia Ph: +61 2 9555 5570 Fx: +61 2 9555 5571 email: info@interspire.com web: http://www.interspire.com
On Thu, Apr 07, 2005 at 12:02:24PM +1000, Neil Conway wrote:
> Chris Smith wrote:
> >I'm trying to use a limit clause with delete, but it doesn't work at the
> >moment
>
> It isn't in the SQL standard, and it would have undefined behavior: the
> sort order of a result set without ORDER BY is unspecified, so you would
> have no way to predict which rows DELETE would remove.
>
> >delete from table where x='1' limit 1000;
>
> You could use a subquery to achieve this:
>
> DELETE FROM table WHERE x IN
> (SELECT x FROM table ... ORDER BY ... LIMIT ...);
In particular, x can be the system column "ctid", a trick which could be
useful to remove duplicates, for example.
--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Porque Kim no hacia nada, pero, eso sí,
con extraordinario éxito" ("Kim", Kipling)
For the archives... Using 7.4 so IN() is a little slower, so I rewrote it slightly to be DELETE FROM table WHERE EXISTS (select x from table .... LIMIT ...); Works very nicely :) Thanks again. Neil Conway wrote: > Chris Smith wrote: > >> I'm trying to use a limit clause with delete, but it doesn't work at >> the moment > > > It isn't in the SQL standard, and it would have undefined behavior: the > sort order of a result set without ORDER BY is unspecified, so you would > have no way to predict which rows DELETE would remove. > >> delete from table where x='1' limit 1000; > > > You could use a subquery to achieve this: > > DELETE FROM table WHERE x IN > (SELECT x FROM table ... ORDER BY ... LIMIT ...); > > -Neil > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Regards, Chris Smith Unit 2, 3 National Street, Rozelle, NSW 2039 Australia Ph: +61 2 9555 5570 Fx: +61 2 9555 5571 email: info@interspire.com web: http://www.interspire.com
Hi Chris, Just a thought: if you have to clear the table anyway, wouldn't it work for you to use truncate ? That should be faster than delete. HTH, Csaba. On Thu, 2005-04-07 at 04:11, Chris Smith wrote: > I don't care about the order in my particular case, just that I have to > clear the table. > > I'll try the subquery and see how I go :) > > Thanks! > > Neil Conway wrote: > > Chris Smith wrote: > > > >> I'm trying to use a limit clause with delete, but it doesn't work at > >> the moment > > > > > > It isn't in the SQL standard, and it would have undefined behavior: the > > sort order of a result set without ORDER BY is unspecified, so you would > > have no way to predict which rows DELETE would remove. > > > >> delete from table where x='1' limit 1000; > > > > > > You could use a subquery to achieve this: > > > > DELETE FROM table WHERE x IN > > (SELECT x FROM table ... ORDER BY ... LIMIT ...); > > > > -Neil > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
Hi Csaba, Because I have a where clause limiting which records I'm deleting. I'm deleting old info from a database, so I'm doing: DELETE FROM sessions WHERE EXISTS (SELECT sessiontime FROM sessions WHERE sessiontime < (timenow-7days) LIMIT 100) (timenow-7days is evaluated in PHP and made an int). So every time the page gets hit, I'm deleting up to 100 records that are older than 7 days.. Csaba Nagy wrote: > Hi Chris, > > Just a thought: if you have to clear the table anyway, wouldn't it work > for you to use truncate ? That should be faster than delete. > > HTH, > Csaba. > > On Thu, 2005-04-07 at 04:11, Chris Smith wrote: > >>I don't care about the order in my particular case, just that I have to >>clear the table. >> >>I'll try the subquery and see how I go :) >> >>Thanks! >> >>Neil Conway wrote: >> >>>Chris Smith wrote: >>> >>> >>>>I'm trying to use a limit clause with delete, but it doesn't work at >>>>the moment >>> >>> >>>It isn't in the SQL standard, and it would have undefined behavior: the >>>sort order of a result set without ORDER BY is unspecified, so you would >>>have no way to predict which rows DELETE would remove. >>> >>> >>>>delete from table where x='1' limit 1000; >>> >>> >>>You could use a subquery to achieve this: >>> >>>DELETE FROM table WHERE x IN >>> (SELECT x FROM table ... ORDER BY ... LIMIT ...); >>> >>>-Neil >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 2: you can get off all lists at once with the unregister command >>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >>> > > > -- Regards, Chris Smith Unit 2, 3 National Street, Rozelle, NSW 2039 Australia Ph: +61 2 9555 5570 Fx: +61 2 9555 5571 email: info@interspire.com web: http://www.interspire.com
On Thu, Apr 07, 2005 at 11:51:10 +1000, Chris Smith <chris@interspire.com> wrote: > > Is there another way to approach this? > > I'm trying to delete records through a webapp and if there are 500,000 > records for example, I can't really leave the page open and expect it to > finish... Maybe you could use a statement timeout and schedule a batch delete if the delete times out?