1

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?

1
  • Can you explain what you want to achieve with that? Jun 24 at 1:54
4

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.