PostgreSQL重置所有表Sequence

什么是sequence？

sequence 是 pg 自带的高效的自增id工具（也叫序列）。sequence 使用了轻量级锁的方式来做到高效自增id的，所以会比 UPDATE 行锁快。sequence 的返回数据类型默认是64位的整数，pg 10 可以自定 smallint, integer 或者是 bigint。

为什么要重置sequence？

我目前使用的数据库时PostgreSQL 12，在将数据用Navcat导入之后。EF执行插入时报错，提示ID已经存在。最开始因为不了PostgreSQL的自增机制，还以为是EF有问题。查询资料后发现是因为sequence没有更新引起的错误，手动通过命令重置后就可以正常插入数据了。

在设计数据库，因为主键字段都是id，且设置了自增，便编写了一个脚本，将所有包含id自增字段的表的sequence重置为目前最大id。以后导入数据后重新执行一遍即可。

DO $$ DECLARE
TABLE_NAME TEXT;
maxid INT;
BEGIN
        FOR TABLE_NAME IN (
        SELECT
            tb.TABLE_NAME 
        FROM
            information_schema.tables AS tb
            INNER JOIN information_schema.COLUMNS AS cols ON tb.TABLE_NAME = cols.TABLE_NAME 
        WHERE
            tb.table_catalog = 'dncsdb' 
            AND tb.table_schema = 'public' 
            AND cols.COLUMN_NAME = 'id' 
        )
        LOOP
        EXECUTE'SELECT MAX(id) +1 FROM ' || TABLE_NAME || ';' INTO maxid;
    IF
        maxid IS NOT NULL THEN
            raise notice '%',
            'set sequence ' || TABLE_NAME || '_id_seq  restart with ' || maxid;
        EXECUTE 'alter sequence ' || TABLE_NAME || '_id_seq  restart with ' || maxid || ';';

    END IF;

END LOOP;

END $$;

或者可以通过以下命令手动进行重置。

查询所有的sequence：

select relname from pg_class where relkind='S' ;

查询表的最大id值：

select max(id) + 1 as seqid from content_info

重置sequence：

alter sequence your_sequence restart with your_value;