postgresql批量修改序列自增

当数据库备份或迁移时，数据库序列出现从1自增的情况，在jpa save数据时会遇到id已存在的情况，则有下列方法可以修改自增序列的值

DO $$
DECLARE
    sequence_name RECORD;
    max_id INTEGER;
    sequence_base_name TEXT;
BEGIN
    FOR sequence_name IN
        SELECT s.sequence_name
        FROM information_schema.sequences AS s
        WHERE s.sequence_schema = 'public'
        ORDER BY s.sequence_name ASC
    LOOP
        BEGIN
            -- 经查看大部分得到的序列名去掉_id_seq就是表名
            -- 获取序列的基础名称
            sequence_base_name := regexp_replace(sequence_name.sequence_name, '_id_seq$', '');

            -- 查询对应表的最大ID值
            EXECUTE format('SELECT MAX(id) AS max_id FROM %I', sequence_base_name) INTO max_id;

            -- 更新序列
            IF max_id IS NOT NULL THEN
                EXECUTE format('ALTER SEQUENCE %I RESTART WITH %s', sequence_name.sequence_name, max_id + 1);
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                -- 捕获并跳过错误（可能遇到去掉'_id_seq$'后之后的表名不存在情况）
                RAISE NOTICE 'Error processing sequence %: %', sequence_name.sequence_name, SQLERRM;
        END;
    END LOOP;
END $$;

若序列名与表名没有逻辑

--查看表的列序列
SELECT table_name, column_name, column_default
FROM information_schema.columns
WHERE column_default LIKE 'nextval(%' 
  AND table_schema = 'public';
--修改序列的last_value 
ALTER SEQUENCE <sequence_name> RESTART WITH alter_value;
--查看
SELECT last_value FROM <sequence_name>;