天天快讯:批量更新Postgresql的序列
2023-04-24 18:42:54
序列（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()来判断是否为自增的列。
在更新表的序列时，请注意以下几点：
序列是全局对象，因此在更新前，请确保没有其他用户当前正在使用该序列。一定多检查，不要更新错误的序列。操作之前先备份数据。