使用数值重置列，该数值表示销毁行时的顺序

Question

我有一个用户表，其中有一个名为order的列，表示他们将被选择的顺序。

因此，例如，该表可能看起来像：

|id|name|order||-----|--------|-------||1|John |2||2|Mike |0||3|Lisa|1|

所以，假设现在Lisa被销毁了，我希望在我销毁Lisa的同一事务中，我能够更新表，这样订单仍然一致，所以预期结果是：

|id|name|order||-----|--------|-------||1|John |1||2|Mike |0|

或者，如果Mike是要删除的人，则预期结果为：

|id|name|order||-----|--------|-------||1|John |1||3|Lisa|0|

我如何在PostgreSQL中做到这一点？

Answer 1

如果只删除一行，则一个选项使用cte和returning子句来触发update

with del as (
delete from mytable where name = 'Lisa'
returning ord
)
update mytable 
set ord = ord - 1
from del d
where mytable.ord > d.ord

作为一种更通用的方法，我真的建议在每次删除后对整个表重新编号。这是低效的，并且对于多行删除来说可能会变得乏味。

相反，您可以在表的顶部构建一个视图：

create view myview as
select id, name, row_number() over(order by ord) ord
from mytable