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:
================================================================
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 <nagata@sraoss.co.jp>