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