PostgreSQL-重新编号列 [英] Postgresql - Renumber columns
本文介绍了PostgreSQL-重新编号列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要重新编号数据库的行。删除某些行后,我必须重新编号某些列。如何使用 postgresql
做到这一点?
I need to renumber the rows of my database. After I delete some rows I have to renumber certain column. How can I do that using postgresql
?
更新:
例如:
我有一个这样的表:
UPDATE: For example: I have a table like this:
ac_n_circ name
1 x
2 y
8 c
15 b
我想像这样对表重新编号:
And I want to renumber this table like this:
ac_n_circ name
1 x
2 y
3 c
4 b
是否有任何算法或类似方法可以执行此操作
Is there any algorithm or something like that to do this in posgresql?
谢谢!
推荐答案
警告:
仅当 ac_n_circ
不是主键列时才有意义。
如果您确定需要这个(真的吗?),则应执行以下操作:
Caution:
This only makes sense if ac_n_circ
is NOT the primary key column.
If you are sure you need this (are you really?), then something like the following should work:
with new_numbers as (
select row_number() over (order by ac_n_circ) as new_nr,
ac_n_circ,
id
from foo
)
update foo
set ac_n_circ = nn.new_nr
from new_numbers nn
where nn.id = foo.id;
或者:
update foo
set ac_n_circ = nn.new_number
from (
select id,
ac_n_circ,
row_number() over (order by ac_n_circ) as new_number
from foo
) nn
where nn.id = foo.id;
这两个语句均假定存在名为 id $的主键列c $ c>。
Both statements assume that there is a primary key column named id
.
这篇关于PostgreSQL-重新编号列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文