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

在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，對嗎？）相關：

• 如何防止 PostgreSQL 觸發器被另一個觸發器觸發？
• 更新 columnA 或 ColumnB 或 ColumnC 時觸發觸發

您可以通過使用觸發表的名稱（安全！）使觸發器函式適用於任何給定的表（帶有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