Postgresql
如何在更新時自動增加串列列?
在Postgres 10 中創建序列時,如何自動增加更新?(不僅僅是為下一個插入的行分配下一個更高的數字。)
例如,假設我創建了在此頁面上找到的下表和序列:
CREATE TABLE fruits( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL ); INSERT INTO fruits(name) VALUES('Orange'); INSERT INTO fruits(id,name) VALUES(DEFAULT,'Apple'); SELECT * FROM fruits; id | name ----+-------- 1 | Apple 2 | Orange (2 rows)
如您所見,它在插入時正確地將“id”列自動遞增一。但是,如果我進行如下更新:
update fruits set name = 'Orange2' where name = 'Orange'; SELECT * FROM fruits; id | name ----+-------- 1 | Apple How do I get this to auto-increment to 3? --> 2 | Orange2 (2 rows)
如上所示,id=2 的行沒有更新為 id=3。如何創建一個在更新時自動遞增的序列?
這是一個不尋常的請求,因為人們通常不想更改現有的序列號。(PK,不少!)
但要回答這個問題:使用**觸發器**。
觸發函式和触發器範例:
CREATE FUNCTION trg_next_id_on_update() RETURNS trigger LANGUAGE plpgsql AS $func$ BEGIN NEW.id := nextval(pg_get_serial_sequence('public.fruits', 'id')); RETURN NEW; END $func$; CREATE TRIGGER next_id_on_update BEFORE UPDATE ON fruits FOR EACH ROW WHEN (NEW.name <> OLD.name) EXECUTE FUNCTION trg_next_id_on_update();
db<>fiddle here(對於 Postgres 10)
當然,這會覆蓋
id
對同一列中可能已進行的任何更改UPDATE
。而且它並不完全是“自動增量”。它從附件中分配下一個空閒號碼
SEQUENCE
。假設這就是你的意思。注意
WHEN
條件:觸發器僅在name
實際更改時觸發。(不想更改其他更新的 ID,對嗎?)相關:您可以通過使用觸發表的名稱(安全!)使觸發器函式適用於任何給定的表(帶有
serial
命名id
的):... NEW.id := nextval(pg_get_serial_sequence(quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME), 'id')); ...
閱讀有關觸發功能的手冊。
有關的:
或者通過硬編碼的名稱使其不那麼通用並且更快一點
SEQUENCE
:... NEW.id := nextval('public.fruits_id_seq'); ...
您決定在您的環境中什麼是安全和適當的。
但是你的例子在約束
UPDATE
下更有意義:UNIQUE
fruits.name
CREATE TABLE public.fruits ( id serial PRIMARY KEY , name varchar NOT NULL UNIQUE -- ! );