PG把所有序列更新到表id最大值+1
DO $$
DECLARE
rec RECORD;
max_id bigint;
seq_name TEXT;
sql_str TEXT;
BEGIN
-- 遍历每个表
FOR rec IN
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema = 'manage' -- 或者你的模式
AND column_default LIKE 'nextval(%' -- 确保只选择包含序列的列
LOOP
-- 构建序列名
seq_name := rec.table_name || '_' || rec.column_name || '_seq';
-- 检查序列是否存在
IF EXISTS (
SELECT 1 FROM information_schema.sequences
WHERE sequence_schema = 'manage' -- 或者你的模式
AND sequence_name = seq_name
) THEN
-- 获取每个表的最大ID
EXECUTE 'SELECT MAX(' || quote_ident(rec.column_name) || ') FROM manage.' || quote_ident(rec.table_name) INTO max_id;
-- 设置序列的当前值
sql_str := 'SELECT setval(' || quote_literal('manage.'||seq_name) || ', ' || (max_id + 1) || ')';
IF sql_str IS NOT NULL THEN
EXECUTE sql_str;
END IF;
ELSE
RAISE NOTICE '序列 % 不存在', seq_name;
END IF;
END LOOP;
END $$;
发表于 2024-02-08 11:52:48 并被添加「」标签,阅读量: 2675 。
此处评论已关闭