How to auto-increment a serial column ON UPDATE?

Question

When creating a sequence in Postgres 10, how do you auto-increment on updates? (Not just assign the next higher number for the next inserted row.)

For example, suppose I create the following table and sequence found on this page:

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)

As you can see it correctly auto-increments the "id" column by one on inserts. However, if I do an update like:

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)

As you can see above, the row with id=2 has not been updated to id=3. How do I create a sequence that will auto-increment on updates?

Answer 1

It's an unusual request, because one wouldn't typically want to change an existing serial ID. (The PK, no less!)
But to answer the question: Use a trigger.

Example trigger function & trigger:

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

Of course, this overwrites any changes to the id column that may have been made in the same UPDATE.

And it does not exactly "auto-increment". It assigns the next free number from the attached SEQUENCE. Assuming that's what you meant.

Note the WHEN condition: the trigger is only fired when the name actually changes. (Wouldn't want to change the ID for other updates, would you?) Related:

• How to prevent a PostgreSQL trigger from being fired by another trigger?
• Fire trigger on update of columnA or ColumnB or ColumnC

You can make the trigger function work for any given table (with a serial named id) by using the name of the triggering table (safely!):

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

Read the manual about trigger functions.
Related:

• Prevent explicit insert into serial columns

Or make it less generic and a bit faster by hard-coding the name of the SEQUENCE:

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

You decide what's safe and appropriate in your environment.

But your UPDATE example makes more sense with a UNIQUE constraint on fruits.name:

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

Answer 2

if you want to explicitly update it you can use the keyword DEFAULT

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

if you want to implicitly update it use a trigger like in the other answer.