A rather obvious use case which Tom Lane et al. seem arrogantly oblivious to: pruning, or keeping only the top X rows. It's both a pain and a performance degradation, not to mention far less readable as well as somewhat ambiguous with regards to concurrency safety, to have to do:DELETE FROM tweets WHERE ctid = ANY(ARRAY(SELECT ctid FROM tweets ORDER BY time DESC OFFSET 1000));Or, as in my use case:DELETE FROM tweets WHERE ctid = ANY(ARRAY(SELECT ctid FROM (SELECT ROW_NUMBER() OVER (PARTITION BY account ORDER BY time DESC) a, ctid FROM tweets) a WHERE a > 1000));
Yes, we need it.Sample:- Delete duplicates- update top x rowsLegacy Systems can you find every day. you must only wait long enough :)The World is not ideal, but SQL must query it
If we need limit clause for update, after that, maybe! we need order by.UPDATE `Product_download` SET `version`='9.2.1' WHERE `product_download_id`=367 LIMIT 1 order by product_download_at;
No way at all. I believe if you end up with such an UPDATE statement you are filtering data with the wrong clause. So it is either a fault in your database design or in your understanding of the data. The only reason to support something like that is for legacy data coming up from legacy systems where the design was so horrible you cannot filter data at all.