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));
tom - 11 years ago
Yes, we need it.
Sample:
- Delete duplicates
- update top x rows
Legacy Systems can you find every day. you must only wait long enough :)
The World is not ideal, but SQL must query it
Hossein zolfi - 11 years ago
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.
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 rows
Legacy 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.