序列（sequence）是 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()來判斷是否為自增的列。
註意事項
在更新表的序列時，請註意以下幾點：
- 序列是全局對象，因此在更新前，請確保沒有其他用戶當前正在使用該序列。
- 一定多檢查，不要更新錯誤的序列。
- 操作之前先備份數據。
