PostgreSQL重置所有表Sequence

最新推荐文章于 2026-04-17 22:36:49 发布

原创

最新推荐文章于 2026-04-17 22:36:49 发布 · 2.3k 阅读

本文介绍了PostgreSQL中sequence作为自增ID工具的高效特性，以及在数据导入后因sequence未更新导致的插入错误。解决方法是通过查询最大ID并重置sequence。提供了一段PL/pgSQL脚本来批量重置所有包含'id'字段的表的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 '%