序列（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()來判斷是否為自增的列，
注意事項
在更新表的序列時，請注意以下幾點：
- 序列是全域物件，因此在更新前，請確保沒有其他用戶當前正在使用該序列，
- 一定多檢查，不要更新錯誤的序列，
- 操作之前先備份資料，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/551134.html
標籤：PostgreSQL
上一篇：day05-優惠券秒殺01
下一篇：返回列表