PostgreSQL重置所有Sequence启始值
适用范围
pg12+
方案概述
在从ORACLE迁移到PG的过程中，当导入数据后，在启动应用时,Sequence 往往是从1开始，数表的的数据肯定是是超过1的，这时插入数据库会失败。
这时需要我们手动重设Sequence ，以保证Sequence 能正常插入到中而与表里原来的数据不冲突。
实施步骤
1.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 $$;
2.sequence不是表的自增主键
如有SEQ 没有在建表语句中，而是在使用的才用，那么这时就不好判断， 这个SEQ属于那一个表.如果不知道，那个SEQ属于那个表， 我们以最大表为基准。 将所有SEQ的启始值，从最大表的行数开始
#a.指定用户查询表的行数
SELECT
relname,relowner,
reltuples
FROM
pg_class CLS
LEFT JOIN pg_namespace N ON ( N.oid = CLS.relnamespace )
LEFT JOIN pg_authid au ON (CLS.relowner=au.oid)
WHERE
nspname NOT IN ( 'pg_catalog', 'information_schema','postgres' )
and au.rolname='ahser'
AND relkind = 'r'
ORDER BY
reltuples DESC;
#b.根据上表查询的最大行数值 ，浮动1000 在设置， 我们查最大表行数为44000，而且通过 count计算也确实是44000，所以我们直接将所有SEQ的起始值设为44000
select 'alter sequence IF EXISTS '||relname ||' start with 44000 MAXVALUE 99999999;;'
from pg_class CLS
LEFT JOIN pg_authid au ON (CLS.relowner=au.oid)
where relkind='S'
and au.rolname='ahser';
#c.在psql中执行 b所生成的动态SQL语句。
