On Fri, 17 Dec 2021 09:47:18 +0900 Yugo NAGATA <nag...@sraoss.co.jp> wrote:
> Hello hackers, > > We cannot use ORDER BY or LIMIT/OFFSET in the current > DELETE statement syntax, so all the row matching the > WHERE condition are deleted. However, the tuple retrieving > process of DELETE is basically same as SELECT statement, > so I think that we can also allow DELETE to use ORDER BY > and LIMIT/OFFSET. > > Attached is the concept patch. This enables the following > operations: After post this, I noticed that there are several similar proposals in past: https://www.postgresql.org/message-id/flat/AANLkTi%3D6fBZh9yZT7f7kKh%2BzmQngAyHgZWBPM3eiEMj1%40mail.gmail.com https://www.postgresql.org/message-id/flat/1393112801.59251.YahooMailNeo%40web163006.mail.bf1.yahoo.com https://www.postgresql.org/message-id/flat/CADB9FDf-Vh6RnKAMZ4Rrg_YP9p3THdPbji8qe4qkxRuiOwm%3Dmg%40mail.gmail.com https://www.postgresql.org/message-id/flat/CALAY4q9fcrscybax7fg_uojFwjw_Wg0UMuSrf-FvN68SeSAPAA%40mail.gmail.com Anyway, I'll review these threads before progressing it. > > ================================================================ > postgres=# select * from t order by i; > i > ---- > 1 > 2 > 2 > 2 > 2 > 5 > 10 > 20 > 33 > 35 > 53 > (11 rows) > > postgres=# delete from t where i = 2 limit 2; > DELETE 2 > postgres=# select * from t order by i; > i > ---- > 1 > 2 > 2 > 5 > 10 > 20 > 33 > 35 > 53 > (9 rows) > > postgres=# delete from t order by i offset 3 limit 3; > DELETE 3 > postgres=# select * from t order by i; > i > ---- > 1 > 2 > 2 > 33 > 35 > 53 > (6 rows) > ================================================================ > > Although we can do the similar operations using ctid and a subquery > such as > > DELETE FROM t WHERE ctid IN (SELECT ctid FROM t WHERE ... ORDER BY ... LIMIT > ...), > > it is more user friendly and intuitive to allow it in the DELETE syntax > because ctid is a system column and most users may not be familiar with it. > > Although this is not allowed in the SQL standard, it is supported > in MySQL[1]. DB2 also supports it although the syntax is somewhat > strange.[2] > > Also, here seem to be some use cases. For example, > - when you want to delete the specified number of rows from a table > that doesn't have a primary key and contains tuple duplicated. > - when you want to delete the bottom 10 items with bad scores > (without using rank() window function). > - when you want to delete only some of rows because it takes time > to delete all of them. > > [1] https://dev.mysql.com/doc/refman/8.0/en/delete.html > [2] > https://www.dba-db2.com/2015/04/delete-first-1000-rows-in-a-db2-table-using-fetch-first.html > > How do you think it? > > -- > Yugo NAGATA <nag...@sraoss.co.jp> -- Yugo NAGATA <nag...@sraoss.co.jp>
