批量更新Postgresql的序列

2023-04-24 13:41 由波多尔斯基发表于 #数据库

序列（sequence）是 PostgreSQL 中的一种对象，用于生成自动递增的唯一标识符。通常，序列会与表的自增主键一起使用，以确保每个新插入的行都有一个唯一的标识符。在某些情况下，可能需要更新序列的值：

从另一个数据库中导入数据，自增列的值也从原来的数据中导入。导入的过程中，目标数据库的序列不会得到更新，这样如果执行数据库的插入操作，会出现主键冲突的问题。（感觉非常莫名其妙）

如果数据不是很多的情况下，可以通过多次插入，每次都忽略错误，最后序列自增上来了，就可以插入成功了。

本文将介绍如何查询和更新 PostgreSQL 表的序列，并写一个存储过程进行批量操作。

序列与自增主键

在 PostgreSQL 中，序列是由一个名称、一个当前值和递增步长组成的对象。表的自增主键通常依赖于序列来生成唯一的标识符。以下 SQL 语句创建了一个名为 my_table 的表，该表包含一个自增主键列 id：

CREATE TABLE my_table (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

SERIAL 类型实际上是一个整数类型，并且在表中创建一个名为 my_table_id_seq 的序列对象。每当您向表中插入一行时，PostgreSQL 将自动递增序列并将其值分配给 id 列。

查询表的序列

要查询表的序列，在 PostgreSQL 中，您可以执行以下 SQL 语句：

SELECT pg_get_serial_sequence('my_table', 'id');

这将返回与 my_table 表的 id 列对应的序列名称。请注意，参数的第一个要为标准名称，第二列则需要是纯字符串，对于有大小写的情况，要注意引号的用法：

SELECT pg_get_serial_sequence('"AData"', 'Id');

更新表的序列

要更新表的序列，可以使用setval方法。以下 SQL 语句将将序列 my_table_id_seq 的下一个值设置为 100：

SELECT setval('my_table_id_seq', 100);

我们可以统计当前的最大值，直接将最大值+1赋值给它，对于大小写的情况，也得注意：

SELECT SETVAL('"AData_Id_seq"', (SELECT MAX("Id") + 1 FROM "AData"));

自动化操作

一个个调用还是非常麻烦，我创建了一个函数，可以用来批量更新指定schema内的序列，并利用临时表返回更新的表格与更新的结果。

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;
	
SELECT * FROM update_sequence_values();

注意：

格式化字符%s与%I有不同，在 PostgreSQL 中，%I 是格式化字符串中的一个占位符，用于在 SQL 查询中引用标识符（如列名、表名等）。它类似于 %s 占位符，但是会将参数中的标识符转换为带有双引号的字符串，以防止 SQL 注入攻击。对于setval参数，需要灵活选择使用%s与%I

函数使用is_identity()来判断是否为自增的列。

注意事项

在更新表的序列时，请注意以下几点：

序列是全局对象，因此在更新前，请确保没有其他用户当前正在使用该序列。
一定多检查，不要更新错误的序列。
操作之前先备份数据。

热门相关：帝少的专属：小甜心，太缠人霸皇纪天启预报刺客之王刺客之王