批量更新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();

注意:

  1. 格式化字符%s%I有不同,在 PostgreSQL 中,%I 是格式化字符串中的一个占位符,用于在 SQL 查询中引用标识符(如列名、表名等)。它类似于 %s 占位符,但是会将参数中的标识符转换为带有双引号的字符串,以防止 SQL 注入攻击。对于setval参数,需要灵活选择使用%s与%I
  2. 函数使用is_identity()来判断是否为自增的列。

注意事项

在更新表的序列时,请注意以下几点:

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

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