为所有表压缩或重新编号ID，并将序列重置为max（id）？

Question

长时间运行后，我的id字段上出现了越来越多的空洞。一些表的id是int32类型，而id序列正在接近其最大值。有些Java源代码是只读的，因此我不能简单地将id列类型从int32更改为long，这会破坏API。

我想重新编号它们。这可能不是好的做法，但好坏不是这个问题关心的重点。我想重新编号，特别是那些非常长的ID，如“61789238”、“548273826529524324”。我不知道为什么它们这么长，但较短的ID也更容易手动处理。

但由于引用和约束关系，手动压缩ID并不容易。

PostgreSQL本身是否支持ID重新编号？或者是否有任何插件或维护实用程序可执行此任务？

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

- Lenik

Answer 1

这个问题已经很老了，但是在dba.SE上有一个绝望的用户尝试应用建议并提出了一个新问题。请到以下链接查找更详细的解释和说明：

• 压缩PostgreSQL中的序列

当前被接受的答案对于大多数情况都会失败。

通常，您在id列上有一个PRIMARY KEY或UNIQUE约束条件，默认情况下它是NOT DEFERRABLE的。（OP提到了references and constraints。）此类约束条件在每行后进行检查，因此尝试时最可能会发生唯一性冲突错误。详情请参阅以下内容：

• 定义为DEFERRABLE INITIALLY IMMEDIATE的约束条件仍然是DEFERRED吗？

通常，人们希望在关闭间隙的同时保留原始行的顺序。但是更新行的顺序是任意的，导致出现任意数量的数字。演示的示例似乎保留了原始序列，因为物理存储仍然与所需顺序相一致（刚刚一个时刻之前按照所需的顺序插入了行），这在现实世界的应用程序中几乎从未发生过，并且完全不可靠。

事情比起初看起来更加复杂。如果您可以暂时删除PK / UNIQUE约束（以及相关的FK约束条件），则一种解决方案（其中之一）：

BEGIN;

LOCK tbl;

-- remove all FK constraints to the column

ALTER TABLE tbl DROP CONSTRAINT tbl_pkey;  -- remove PK

-- for the simple case without FK references - or see below:    
UPDATE tbl t  -- intermediate unique violations are ignored now
SET    id = t1.new_id
FROM  (SELECT id, row_number() OVER (ORDER BY id) AS new_id FROM tbl) t1
WHERE  t.id = t1.id;

-- Update referencing value in FK columns at the same time (if any)

SELECT setval('tbl_id_seq', max(id)) FROM tbl;  -- reset sequence

ALTER TABLE tbl ADD CONSTRAINT tbl_pkey PRIMARY KEY(id); -- add PK back

-- add all FK constraints to the column back

COMMIT;

对于大表格来说，这也更快，因为检查每一行的PK（和FK）约束要比删除约束然后重新添加约束的代价高得多。

如果其他表中有引用tbl.id的FK列，请使用数据修改CTE来更新所有这些表中的数据。

例如，对于一个名为fk_tbl的表格和一个FK列fk_id：

WITH u1 AS (
   UPDATE tbl t
   SET    id = t1.new_id
   FROM  (SELECT id, row_number() OVER (ORDER BY id) AS new_id FROM tbl) t1
   WHERE  t.id = t1.id
   RETURNING t.id, t1.new_id  -- return old and new ID
   )
UPDATE fk_tbl f
SET    fk_id = u1.new_id      -- set to new ID
FROM   u1
WHERE  f.fk_id = u1.id;       -- match on old ID

更多内容请参见dba.SE上的参考答案。

- Erwin Brandstetter

---

还有另一种方法：重命名 id 列，添加一个新的序列 id 列；对于引用 FK，也是同样的操作，然后使用 {oldid, newid} 更新引用 FK，最后删除 {oldid, oldFK}。重命名的顺序可以变化；在极端情况下，旧的和新的 id 和 FK 可以共存，使得旧方案在工作进行中仍然存在。我需要详细说明吗？ - joop

@joop：你可以在这里添加另一个详细答案，或者更好的是，在dba.SE上的新问题下添加一个更实质性的答案。 - Erwin Brandstetter

我在那里没有账号（什么？没有单点登录？），所以我会在这里发布。 - joop

@joop：您可以使用现有的StackExchange帐户在dba.se上进行“注册”。 - user330315

Answer 2

假设您的ID是由 bignum 序列生成的，只需 RESTART 该序列并使用 idcolumn = DEFAULT 更新表即可。

注意：如果其他表使用此 id 列作为外键，请确保打开了 on update cascade 修饰符。

例如：

创建表格、添加一些数据并删除中间值：

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)

- unpythonic

---

对于大多数用例，这不会按预期工作。请考虑添加的答案中的细节。 - Erwin Brandstetter

一个小技巧可以让这个答案在所有情况下都起作用：你只需要将id重新编号为一些独特的更高数字，确保不会干扰新的紧凑id。因此，在运行上面的答案之前，只需执行以下操作： UPDATE foo SET id = id + (SELECT max(id) FROM foo); - Robert Špendl

Answer 3

在旧的id列仍在使用的情况下，新增id列和外键(s)。通过一些（快速）重命名，应用程序不必意识到这一变化。（但是在最终重命名步骤期间应用程序应该处于非活动状态）

\i tmp.sql
    -- the test tables
CREATE TABLE one (
    id serial NOT NULL PRIMARY KEY
    , payload text
    );
CREATE TABLE two (
    id serial NOT NULL PRIMARY KEY
    , the_fk INTEGER REFERENCES one(id)
            ON UPDATE CASCADE ON DELETE CASCADE
    );
    -- And the supporting index for the FK ...
CREATE INDEX ON two(the_fk);

    -- populate
INSERT INTO one(payload)
SELECT x::text FROM generate_series(1,1000) x;

INSERT INTO two(the_fk)
SELECT id FROM one WHERE random() < 0.3;

    -- make some gaps
DELETE FROM one WHERE id % 13 > 0;

-- SELECT * FROM two;

    -- Add new keycolumns to one and two
ALTER TABLE one
    ADD COLUMN new_id SERIAL NOT NULL UNIQUE
    ;

    -- UPDATE:
    -- This could need DEFERRABLE
    -- Note since the update is only a permutation of the
    -- existing values, we dont need to reset the sequence.
UPDATE one SET new_id = self.new_id
FROM ( SELECT id, row_number() OVER(ORDER BY id) AS new_id FROM one ) self
WHERE one.id = self.id;

ALTER TABLE two
    ADD COLUMN new_fk INTEGER REFERENCES one(new_id)
    ;

    -- update the new FK
UPDATE two t
SET new_fk = o.new_id
FROM one o
WHERE t.the_fk = o.id
    ;

SELECT * FROM two;

    -- The crucial part: the final renaming
    -- (at this point it would be better not to allow other sessions
    -- messing with the {one,two} tables ...
    -- --------------------------------------------------------------
ALTER TABLE one DROP COLUMN id CASCADE;
ALTER TABLE one rename COLUMN new_id TO id;
ALTER TABLE one ADD PRIMARY KEY(id);

ALTER TABLE two DROP COLUMN the_fk CASCADE;
ALTER TABLE two rename COLUMN new_fk TO the_fk;
CREATE INDEX ON two(the_fk);

    -- Some checks.
    -- (the automatically generated names for the indexes
    -- and the sequence still contain the "new" names.)
SELECT * FROM two;
\d one
\d two

更新：添加了新ID的排列（在将其创建为序列之后）。有趣的是：似乎不需要“DEFERRABLE”。

---

- joop

---

一些细节：1: 通常情况下，在关闭间隙时，人们希望保留原始顺序。 ADD COLUMN new_id SERIAL NOT NULL UNIQUE 并不这样做 - 就像目前接受的答案一样。 2: 新的FK约束应该像旧的那样CASCADE。 3: 不需要使用DROP COLUMN the_fk进行CASCADE。 - Erwin Brandstetter

0. 它基本上是作为概念验证而设计的。
1. 你关于顺序的想法是正确的，我没想到有人会对键值的排序感兴趣...
2. 没有级联，删除列在这里不起作用（9.3.5）
3. 同上。2+3 可以很容易地修复（可能需要一些额外的步骤），但1比较困难；至少需要一个 row_number() 加上一个 set_val()。

- joop

添加的语句与不可延迟的约束一起工作，因为它恰好按顺序更新行。窗口函数 row_number() 生成一个有序集合，Postgres 简单地在 UPDATE 中使用它，因此不会出现冲突。然而，这是一个未记录的实现细节，并不能保证在所有实现中都能正常工作或跨 Postgres 版本持续工作。目前被接受的答案以任意顺序更新，几乎肯定会失败。要验证我的解释，请在 UPDATE 的子查询中添加 ORDER BY random()，您将获得唯一违规错误。 - Erwin Brandstetter

我认为这是由于实现细节引起的，但是当逐个对一组N个键值进行置换（到它们自身）时，我希望触摸第一个（或任何一个）已经创建了一个（时间上的）重复项。因此，出于某种原因，PG能够推迟部分检查（在这种特殊情况下）到操作的后期（我们可以称之为“半可推迟”/ -）。仔细想想，这可能是行版本处理的副作用。顺便说一句：将UNIQUE约束添加到new_id可以轻松地推迟到操作的后阶段。 - joop

Postgres不会推迟检查，这一点已经明确记录在案。我们在相关问题下详细讨论了这个问题。我还在我的答案中添加了链接，请逐步阅读。如果按顺序完成，则没有更新的行会违反唯一（PK）约束条件。 - Erwin Brandstetter

注意：在“制造一些间隔”之后添加UPDATE one SET id = id + 17 WHERE id % 3 > 0;将改变顺序并导致新ID的更新失败。 - joop

Answer 4

*此脚本适用于postgresql

这是一个通用的解决方案，适用于所有情况

此查询可查找任何数据库中所有表字段的描述。

WITH description_bd AS (select colum.schemaname,coalesce(table_name,relname) as table_name , column_name, ordinal_position, column_default, data_type, is_nullable, character_maximum_length, is_updatable,description from 
 ( SELECT columns.table_schema as schemaname,columns.table_name, columns.column_name, columns.ordinal_position, columns.column_default, columns.data_type, columns.is_nullable, columns.character_maximum_length, columns.character_octet_length, columns.is_updatable, columns.udt_name
  FROM information_schema.columns 
 ) colum

 full join (SELECT schemaname, relid, relname,objoid,  objsubid, description
 FROM pg_statio_all_tables ,pg_description where pg_statio_all_tables.relid= pg_description.objoid  ) descre
  on descre.relname = colum.table_name and  descre.objsubid=colum.ordinal_position   and  descre.schemaname=colum.schemaname )

这个查询提出了一个解决方案，以修复所有数据库表的顺序（这会在req字段中生成一个查询，用于修复不同表的顺序）。

它找到表的记录数，然后将该数字增加一。

SELECT  table_name, column_name, ordinal_position,column_default, 
   data_type, is_nullable, character_maximum_length, is_updatable, 
   description,'SELECT setval('''||schemaname||'.'|| replace(replace(column_default,'''::regclass)',''),'nextval(''','')||''',    (select max( '||column_name ||')+1  from '|| table_name ||' ), true);' as req
  FROM description_bd where column_default  like '%nextva%' 

- Mesbah Gueffaf

---

你好@Mesbah Gueffaf，如果您格式化SQL语句更好（我的建议是缩短行并保持一致的缩进），并解释语句的确切作用以及为什么它们有效，这将有助于您的答案可读性。 - Niels Abildgaard

感谢 @NielsAbildgaard 的审阅。我们已经为答案添加了更多的解释。希望这个贡献能够帮助社区。 - Mesbah Gueffaf

Answer 5

由于我不喜欢这些答案，我编写了一个名为PL/pgSQL的函数来完成这项工作。它的调用方式如下：

=> SELECT resequence('port','id','port_id_seq');
 resequence   
--------------
 5090 -> 3919

需要三个参数：

1. 表名
2. SERIAL 类型的列名
3. SERIAL 类型所使用的序列名

函数返回一个短报告，说明它所做的事情，并列出序列的先前值和新值。

该函数通过命名列按顺序遍历表格，并对每一行进行 UPDATE。然后设置序列的新值。就是这样。

1. 值的顺序被保留。
2. 没有涉及到临时列或表的 ADD 和 DROP 操作。
3. 不需要删除和添加约束和外键。
4. 当然，最好为那些外键设置 ON UPDATE CASCADE。

代码：

CREATE OR REPLACE FUNCTION resequence(_tbl TEXT, _clm TEXT, _seq TEXT) RETURNS TEXT AS $FUNC$
DECLARE                                            
        _old BIGINT;_new BIGINT := 0;              
BEGIN
        FOR _old IN EXECUTE 'SELECT '||_clm||' FROM '||_tbl||' ORDER BY '||_clm LOOP
                _new=_new+1;
                EXECUTE 'UPDATE '||_tbl||' SET '||_clm||'='||_new||' WHERE '||_clm||'='||_old;
        END LOOP;
        RETURN (nextval(_seq::regclass)-1)||' -> '||setval(_seq::regclass,_new);
END $FUNC$ LANGUAGE plpgsql;

- furiozo ga