SQL 如何在 PostgreSQL 中通过排序删除固定数量的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5170546/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I delete a fixed number of rows with sorting in PostgreSQL?
提问by Whatsit
I'm trying to port some old MySQL queries to PostgreSQL, but I'm having trouble with this one:
我正在尝试将一些旧的 MySQL 查询移植到 PostgreSQL，但我遇到了这个问题：
DELETE FROM logtable ORDER BY timestamp LIMIT 10;
PostgreSQL doesn't allow ordering or limits in its delete syntax, and the table doesn't have a primary key so I can't use a subquery. Additionally, I want to preserve the behavior where the query deletes exactlythe given number or records -- for example, if the table contains 30 rows but they all have the same timestamp, I still want to delete 10, although it doesn't matter which 10.
PostgreSQL 不允许在其删除语法中进行排序或限制，并且该表没有主键，因此我无法使用子查询。此外，我想保留查询完全删除给定数字或记录的行为——例如，如果表包含 30 行但它们都有相同的时间戳，我仍然想删除 10，尽管这无关紧要其中 10.
So; how do I delete a fixed number of rows with sorting in PostgreSQL?
所以; 如何在 PostgreSQL 中通过排序删除固定数量的行？
Edit:No primary key means there's no log_idcolumn or similar. Ah, the joys of legacy systems!
编辑：没有主键意味着没有log_id列或类似的。啊，遗留系统的乐趣！
回答by mu is too short
You could try using the ctid:
您可以尝试使用ctid：
DELETE FROM logtable
WHERE ctid IN (
SELECT ctid
FROM logtable
ORDER BY timestamp
LIMIT 10
)
The ctidis:
该ctid是：
The physical location of the row version within its table. Note that although the
ctidcan be used to locate the row version very quickly, a row'sctidwill change if it is updated or moved byVACUUM FULL. Thereforectidis useless as a long-term row identifier.
行版本在其表中的物理位置。请注意，虽然
ctid可用于非常快速地定位行版本，但如果行ctid被更新或移动，则行将发生更改VACUUM FULL。因此ctid作为长期行标识符是无用的。
There's also oidbut that only exists if you specifically ask for it when you create the table.
还有，oid但只有当您在创建表时特别要求时才存在。
回答by criticus
回答by Konrad Garus
delete from logtable where log_id in (
select log_id from logtable order by timestamp limit 10);
回答by Patrick Hüsler
Assuming you want to delete ANY 10 records (without the ordering) you could do this:
假设您要删除任何 10 条记录（没有排序），您可以这样做：
DELETE FROM logtable as t1 WHERE t1.ctid < (select t2.ctid from logtable as t2 where (Select count(*) from logtable t3 where t3.ctid < t2.ctid ) = 10 LIMIT 1);
For my use case, deleting 10M records, this turned out to be faster.
对于我的用例，删除 10M 记录，结果证明速度更快。
回答by Bernhard
You could write a procedure which loops over the delete for individual lines, the procedure could take a parameter to specify the number of items you want to delete. But that's a bit overkill compared to MySQL.
您可以编写一个循环删除单个行的过程，该过程可以采用一个参数来指定要删除的项目数。但这与 MySQL 相比有点矫枉过正。
回答by user2449151
If you don't have a primary key you can use the array Where IN syntax with a composite key.
如果您没有主键，则可以将数组 Where IN 语法与复合键一起使用。
delete from table1 where (schema,id,lac,cid) in (select schema,id,lac,cid from table1 where lac = 0 limit 1000);
This worked for me.
这对我有用。
