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?