👨‍⚕️ 主页： gis分享者
👨‍⚕️ 感谢各位大佬 点赞👍 收藏⭐ 留言📝 加关注✅!
👨‍⚕️ 收录于专栏：数据库工程师

一、🥇前言

本文讲解postgresql serial自增，自动更新所有库表serial序列当前值的方法，防止插入数据时唯一值冲突问题。希望对您有帮助。

二、🥇自动更新serial序列当前值

1、🥈创建函数

CREATE OR REPLACE FUNCTION "public"."update_sequence_values"() 
  RETURNS TABLE("var_table_schema" text, "var_table_name" text, "old_max_id" int4, "new_max_id" int4) AS $$
DECLARE
  table_rec RECORD;
  max_id INTEGER;
  old_max_id_val INTEGER;
BEGIN
  -- 创建临时表以保存更新的序列值
        DROP TABLE IF EXISTS temp_sequence_updates;
  CREATE TEMP TABLE temp_sequence_updates (
    var_table_schema TEXT,
    var_table_name TEXT,
    old_max_id INTEGER,
    new_max_id INTEGER
  );

  -- 遍历指定模式下所有包含自增主键的表
  FOR table_rec IN (SELECT DISTINCT(table_schema), table_name, is_identity, column_name FROM information_schema.columns WHERE is_identity= 'YES' AND table_schema = 'public') LOOP
    EXECUTE format('SELECT MAX(%I) FROM %I.%I;', table_rec.column_name, table_rec.table_schema, table_rec.table_name) INTO max_id;

    -- 更新序列
    IF max_id IS NOT NULL THEN
      EXECUTE format('SELECT setval(pg_get_serial_sequence(''%I'', ''%s''), %s, false);', table_rec.table_name, replace(table_rec.column_name, '"','') , max_id + 1);

      -- 记录更新操作的日志信息
      INSERT INTO temp_sequence_updates (var_table_schema, var_table_name, old_max_id, new_max_id) VALUES (table_rec.table_schema, table_rec.table_name , max_id, max_id + 1);
    END IF;
  END LOOP;

  -- 返回更新操作的日志信息
  RETURN QUERY SELECT * FROM temp_sequence_updates;
END;
$$ LANGUAGE plpgsql;

2、🥈执行自动扫表，全量更新序列

SELECT * FROM update_sequence_values();