压缩或重新编号所有表的ID，并将序列重置为max（id）？ [英] Compact or renumber IDs for all tables, and reset sequences to max(id)?

问题描述

长时间运行后，我在id字段中得到了越来越多的漏洞。某些表的ID为int32，且ID序列已达到最大值。一些Java源是只读的，因此我不能简单地将id列类型从 int32 更改为 long ，

After running for a long time, I get more and more holes in the id field. Some tables' id are int32, and the id sequence is reaching its maximum value. Some of the Java sources are read-only, so I cannot simply change the id column type from int32 to long, which would break the API.

我想对它们全部重新编号。这可能不是一个好习惯，但此问题与好坏无关。我想重新编号，尤其是那些很长的ID，例如 61789238， 548273826529524324。我不知道为什么它们这么长，但是较短的ID也更容易手动处理。

I'd like to renumber them all. This may be not good practice, but good or bad is not concerned in this question. I want to renumber, especially, those very long IDs like "61789238", "548273826529524324". I don't know why they are so long, but shorter IDs are also easier to handle manually.

但是由于引用和约束，手工压缩ID并不容易

But it's not easy to compact IDs by hand because of references and constraints.

PostgreSQL本身是否支持ID重编号？还是有任何插件或维护实用程序来完成这项工作？

Does PostgreSQL itself support of ID renumbering? Or is there any plugin or maintaining utility for this job?

也许我可以编写一些存储过程？那就太好了，这样我可以每年安排一次。

Maybe I can write some stored procedures? That would be very nice so I can schedule it once a year.

推荐答案

假设您的ID是由<$ c $生成的c> bignum 序列，只需 RESTART 序列并使用 idcolumn = DEFAULT 更新表

Assuming your ids are generated from a bignum sequence, just RESTART the sequence and update the table with idcolumn = DEFAULT.

CAVEAT ：如果此 id 列被其他人用作外键表中，请确保已启用更新级联修饰符。

CAVEAT: If this id column is used as a foreign key by other tables, make sure you have the on update cascade modifier turned on.

例如：

创建表，放入一些数据，然后删除中间值：

Create the table, put some data in, and remove a middle value:

db=# create sequence xseq;
CREATE SEQUENCE
db=# create table foo ( id bigint default nextval('xseq') not null, data text );
CREATE TABLE
db=# insert into foo (data) values ('hello'), ('world'), ('how'), ('are'), ('you');
INSERT 0 5
db=# delete from foo where data = 'how';
DELETE 1
db=# select * from foo;
 id | data  
----+-------
  1 | hello
  2 | world
  4 | are
  5 | you
(4 rows)

重置序列：

db=# ALTER SEQUENCE xseq RESTART;
ALTER SEQUENCE

更新您的数据：

db=# update foo set id = DEFAULT;
UPDATE 4
db=# select * from foo;
 id | data  
----+-------
  1 | hello
  2 | world
  3 | are
  4 | you
(4 rows)

这篇关于压缩或重新编号所有表的ID，并将序列重置为max（id）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！