批次更新Postgresql的序列

2023-04-24 15:00:57

序列(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()來判斷是否為自增的列。

注意事項

在更新表的序列時,請注意以下幾點:

  • 序列是全域性物件,因此在更新前,請確保沒有其他使用者當前正在使用該序列。
  • 一定多檢查,不要更新錯誤的序列。
  • 操作之前先備份資料。