PostgreSql数据库迁移后自增ID引起的序列问题解决
程序员文章站
2022-06-02 17:07:00
...
1、背景
系统在本地测试的时候好好的,结果PG数据库迁移到服务器后进行添加操作的时候会报一个错误:more than one owned sequence found。
结果一查网上有很多说是自增ID迁移数据库后留下的一个坑,是序列产生的问题,找到了一个解决方法,测试有效,记录如下:
2、解决方案
执行sql语句
do --check seq not in sync
$$
declare
_r record;
_i bigint;
_m bigint;
begin
for _r in (
Select
DISTINCT(constraint_column_usage.table_name) as tablename,
constraint_column_usage.column_name as idname,
replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','') as seqname,
columns.table_schema as schamename
from information_schema.constraint_column_usage, information_schema.columns
where constraint_column_usage.table_schema ='public' AND
columns.table_schema = 'public' AND columns.table_name=constraint_column_usage.table_name
AND constraint_column_usage.column_name = columns.column_name
AND columns.column_default is not null
) loop
execute format('select last_value from %I.%s',_r.schamename,_r.seqname) into _i;
execute format('select max(%I) from %I.%I',_r.idname,_r.schamename,_r.tablename) into _m;
if coalesce(_m,0) > _i then
raise info '%',concat('changed: ',_r.tablename||'_Id_seq',' from:',_i,' to:',_m+1);
execute format('alter sequence %I.%s restart with %s',_r.schamename,_r.seqname,_m+1);
end if;
end loop;
end;
$$
;
注意不是public的记得要改一下~
参考:https://www.cnblogs.com/Raspberry-zx/p/12702310.html
上一篇: C语言入门系列之9.预处理
下一篇: 如何在TikTok做好品牌推广?5点建议