PostgreSQL重置所有Sequence启始值
[[toc]]
适用范围
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语句。
免责声明:
1、本站资源由自动抓取工具收集整理于网络。
2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。
3、电子书、小说等仅供网友预览使用,书籍版权归作者或出版社所有。
4、如作者、出版社认为资源涉及侵权,请联系本站,本站将在收到通知书后尽快删除您认为侵权的作品。
5、如果您喜欢本资源,请您支持作者,购买正版内容。
6、资源失效,请下方留言,欢迎分享资源链接
文章评论