postgresql 重置对应表序列最大值

最新推荐文章于 2026-03-10 05:03:14 发布

原创最新推荐文章于 2026-03-10 05:03:14 发布 · 290 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#postgresql #数据库

收录于

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 = 'mdm'
            AND tb.table_schema = 'public'
            AND cols.COLUMN_NAME = 'id' 
            and tb.table_name in ('sys_log')
        )
        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 $$;