Início / dba / Perguntas / 294727
Accepted
user1068636
Asked: 2021-06-24 14:12:58 +0800 CST

Como incrementar automaticamente uma coluna serial ON UPDATE?

  • 772

Ao criar uma sequência no Postgres 10, como você incrementa automaticamente as atualizações? (Não apenas atribua o próximo número mais alto para a próxima linha inserida.)

Por exemplo, suponha que eu crie a seguinte tabela e sequência encontrada nesta página :

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)

Como você pode ver, ele incrementa automaticamente a coluna "id" em um em inserções. No entanto, se eu fizer uma atualização como:

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)

Como você pode ver acima, a linha com id=2 não foi atualizada para id=3. Como crio uma sequência que será incrementada automaticamente nas atualizações?

database-design postgresql

2 respostas

  1. Best Answer

    É uma solicitação incomum, porque normalmente não se deseja alterar um ID serial existente. (O PK, nada menos!)
    Mas para responder a pergunta: Use um gatilho .

    Exemplo de função de gatilho e gatilho:

    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 aqui (para Postgres 10)

    Obviamente, isso substitui quaisquer alterações na idcoluna que possam ter sido feitas no mesmo arquivo UPDATE.

    E não é exatamente "incremento automático". Ele atribui o próximo número livre do anexo SEQUENCE. Supondo que foi isso que você quis dizer.

    Observe a WHENcondição: o gatilho só é acionado quando namerealmente muda. (Não gostaria de alterar o ID para outras atualizações, não é?) Relacionado:

    Você pode fazer a função de gatilho funcionar para qualquer tabela (com um serialnamed id) usando o nome da tabela de gatilho (com segurança!):

       ...
   NEW.id := nextval(pg_get_serial_sequence(quote_ident(TG_TABLE_SCHEMA)
                                  || '.' || quote_ident(TG_TABLE_NAME), 'id'));
   ...

    Leia o manual sobre as funções do gatilho .
    Relacionado:

    Ou torne-o menos genérico e um pouco mais rápido codificando o nome do SEQUENCE:

       ...
   NEW.id := nextval('public.fruits_id_seq');
   ...

    Você decide o que é seguro e apropriado em seu ambiente.

    Mas seu UPDATEexemplo faz mais sentido com uma UNIQUErestrição em fruits.name:

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

  2. se você quiser atualizá-lo explicitamente, você pode usar a palavra-chaveDEFAULT

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

    se você quiser atualizá-lo implicitamente, use um gatilho como na outra resposta.

    • 3

relate perguntas