Postgresql

如何在更新時自動增加串列列?

  • June 25, 2021

在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下更有意義:UNIQUEfruits.name

CREATE TABLE public.fruits (
 id serial PRIMARY KEY
, name varchar NOT NULL UNIQUE -- !
);

引用自:https://dba.stackexchange.com/questions/294727