PostgreSQL 批量创建序列并使用序列

尽兴-

已于 2023-08-17 11:46:00 修改

阅读量1.9k

点赞数 2

分类专栏：后端文章标签：数据库 postgresql sql

于 2023-03-17 11:18:27 首次发布

本文链接：https://blog.csdn.net/m0_47905795/article/details/129615116

版权

后端专栏收录该内容

25 篇文章 1 订阅

订阅专栏

文章讲述了在PG数据库中，如何通过PL/pgSQL脚本批量创建序列，特别是在需要为每张表建立独立自增序列以适应大数据变动时的方法。脚本示例包括动态创建序列，根据表的最大ID设置序列起始值，并修改表的默认值为序列的下一个值，确保数据迁移过程中ID的唯一性。

摘要由CSDN通过智能技术生成

迁移生产数据库过程中发现问题，PG多张表公用一个序列，实际上如果表中数据每日变动量较小的话理论上是可行的。

但是如果太大就考虑需要单独为每张表建立一个序列，并使其id自增。

不过确实没有mysql方便可以直接设置“自增主键”，每次创建序列确实麻烦。

于是...

批量创建序列

注意执行用户权限没有权限可能会查不到数据

- -如果需要按表名动态更改序列名称，可以使用动态SQL语句中的变量来构造序列名。以下是一个示例，用于为数据库中的每个表创建名为"table_name_id_seq"的序列，其中"table_name"是表名：

-- 在这个示例中，我们使用了pg_tables元数据表来获取所有test架构下的表名。你可以根据需要修改条件。

-- 执行此代码段将为数据库中的每个表创建一个名为"table_name_id_seq"的序列，其

中"table_name"是表名， table_schema 表示你的模式名。

DO $$DECLARE
  table_name varchar(255);
  sql_statement varchar(1000);
BEGIN
  FOR table_name IN SELECT tablename FROM pg_tables WHERE schemaname='test'
  LOOP
    sql_statement := 'CREATE SEQUENCE ' || table_name || '_id_seq START 1 INCREMENT 1';
    EXECUTE sql_statement;
  END LOOP;
END$$;

当然，这种做法适用于都是新表的情况下，一般我们的表中都会存在数据，所以我们需要保证我们的新序列的起始值是我们表中的最大值，这样才能保证后续的id不重复。

我们使用EXECUTE语句动态构建了查询最大ID或AutoID值的SQL语句，并使用EXECUTE语句执行该SQL语句，将查询结果存储到'max_id'变量中。这样，就可以正确解析'table_record.table_name'关系


DO $$
DECLARE
  table_record record;
  max_id bigint;
  sql_statement varchar(1000);
  sequence_name varchar(100);
BEGIN
  FOR table_record IN SELECT table_name, column_name
                      FROM information_schema.columns
                      WHERE table_schema='test' AND (column_name = 'autoid' OR column_name = 'id')
  LOOP
    sequence_name := table_record.table_name || '_id_seq';
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = sequence_name ) THEN
      EXECUTE 'SELECT COALESCE(MAX(' || table_record.column_name || '), 0) FROM ' || table_record.table_name INTO max_id;
      sql_statement := 'CREATE SEQUENCE ' || sequence_name || ' START ' || max_id + 1 || ' INCREMENT 1';
      EXECUTE sql_statement;
    END IF;
  END LOOP;
END$$;

批量使用序列

-- 要为每个表的id或auto id列使用对应的序列，可以使用以下示例代码：

-- 在这个示例中，我们使用了information_schema.columns元数据表来获取所有具有'id'或'auto_id'列的表名和列名。你可以根据需要修改条件。

-- 执行此代码段将为每个具有'id'或'auto_id'列的表创建一个名为"table_name_id_seq"的序列，如果存在对应关系，执行跳过。并将每个表的id或auto id列的默认值设置为该序列的下一个值。

DO $$
DECLARE
  table_rec RECORD;
  tablename varchar(255);
  col_name varchar(255);
  sql_statement varchar(1000);
	bool_result BOOLEAN;
BEGIN
  FOR table_rec IN (SELECT table_name, column_name
                    FROM information_schema.columns
                    WHERE table_schema='test' AND (column_name = 'autoid' OR column_name = 'id'))
  LOOP
    tablename := table_rec.table_name;
    col_name := table_rec.column_name;
    sql_statement := 'SELECT EXISTS(SELECT 1 FROM information_schema.sequences WHERE sequence_name = ''' || tablename || '_id_seq'' and sequence_schema = ''iotms'')';
    EXECUTE sql_statement INTO bool_result;
    IF bool_result THEN
      sql_statement := 'ALTER TABLE ' || tablename || ' ALTER COLUMN ' || col_name || ' SET DEFAULT nextval(''' || tablename || '_id_seq''::regclass)';
      EXECUTE sql_statement;
    END IF;
  END LOOP;
END$$;

辅助sql，可以用来验证具体某一序列

-- 授权所有序列给指定用户
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA iotms TO user_iotms

-- 查看序列有哪些表使用
SELECT * from information_schema.sequences WHERE sequence_name = 'devicegroup'

-- 查询出所有表
SELECT TABLENAME from pg_tables WHERE SCHEMANAME = 'test'
-- 查询该模式表下的主键列名称
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema='test' AND (column_name = 'autoid' OR column_name = 'id')
-- 查询每张表下的主键最大值
SELECT COALESCE(MAX( autoid), 0) as maxid FROM yzz_iot_device_branch_20230316
-- 查询序列的当前最大值
select LAST_VALUE from dp_dict_id_seq