Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

-- trigger function
CREATE FUNCTION trg_next_id_on_update()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.id := nextval(pg_get_serial_sequence('fruits', 'id'));
   RETURN NEW;
END
$func$;

-- trigger
CREATE TRIGGER next_id_on_update
BEFORE UPDATE ON fruits
FOR EACH ROW
WHEN (NEW.name <> OLD.name)
EXECUTE PROCEDURE trg_next_id_on_update();

CREATE TABLE

CREATE FUNCTION

CREATE TRIGGER

INSERT INTO fruits(name)    VALUES('Orange');
INSERT INTO fruits(id,name) VALUES(DEFAULT,'Apple');

TABLE fruits;

INSERT 0 1

INSERT 0 1

id
name

1
Orange

2
Apple

SELECT 2

UPDATE fruits
SET    name = 'Orange2'
WHERE  name = 'Orange';

UPDATE 1

TABLE fruits;

id
name

2
Apple

3
Orange2

SELECT 2