How to auto-increment a serial column ON UPDATE?

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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:

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:

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 -- !
);

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply