postgresql - 在postgresql中按行号删除行

Question

我是 postgreSql 的新手，我使用以下查询从数据库中检索所有字段。

SELECT student.*,row_number() OVER () as rnum FROM student;

我不知道如何按行号删除特定行。请给我一些想法。

这是我的 table :

Column      | Type------------+------------------name        | textrollno      | integer cgpa        | double precision department  | text branch      | text 

Answer 1

with a as
(
SELECT student.*,row_number() OVER () as rnum FROM student
)

delete from student where ctid in (select ctid from a where rnum =1) -- the 
                                                                     -- row_number you want    
                                                                     -- to delete

---

引自PostgreSQL - System Columns

ctid :
The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change each time it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. The OID, or even better a user-defined serial number, should be used to identify logical rows.

---

注意:我强烈建议您使用 student 表中的 unique 字段。

---

根据 Craig's comment ，我会给出另一种方法来解决 OP 的问题，这有点棘手

首先为表student创建一个唯一的column，下面query有这个用途

alter table student add column stu_uniq serial

这将为每行生成具有相应唯一值的 stu_uniq，因此 OP 可以使用此 DELETE 任何行轻松 stu_uniq