主页 / dba / 问题 / 294727
Accepted
user1068636
Asked: 2021-06-24 14:12:58 +0800 CST

如何在更新时自动增加串行列？

  • 772

在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。如何创建一个在更新时自动递增的序列？

database-design postgresql

2 个回答

  1. Best Answer

    这是一个不寻常的请求，因为人们通常不想更改现有的序列号。（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 -- !
);
    • 5

  2. 如果要显式更新它，可以使用关键字DEFAULT

    update fruits 
set name = 'Orange2', id=DEFAULT
where name = 'Orange';

    如果您想隐式更新它，请使用另一个答案中的触发器。

    • 3

相关问题