【PostgreSql】more than one owned sequence found
2024-10-22 14:38:05
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;
$$
;
最新文章
- [MySQL5.6 新特性] 全局事务标示符(GTID)
- Android 生成颜色器
- [saiku] 集成单点登录
- isa class 帮助确定对象或变量的数据类型
- 将Android Studio工程导入到Eclipse中
- JAVAMAIL手动发送邮件
- 168. Excel Sheet Column Title
- poj 1007 DNA Sorting
- Use AUTO Mode with FOR XML (SQL Server 2012) Multiple Table
- BZOJ2038小Z的袜子(hose)
- Java调用天气Webservice的小应用
- 不调用库函数实现 strCpy
- Redis 代理 twemproxy
- node.js之调试器
- ASP.NET 页面执行顺序
- Spring Boot - 基础 POM 文件
- C#利用反射来判断对象是否包含某个属性的实现方法
- python的开发环境配置-Eclipse-PyDev插件安装
- Gridview中的选择、删除、编辑、更新、取消留着备用。
- Django之ContentType详解