I have a table that has a primary key based off of a sequence START WITH 1 INCREMENT BY 1.

Primary key works great, incrementing as needed.

However I have another field that is UNIQUE. When I attempt to insert a new row that will fail the UNIQUE check, the primary key is still updated. I expect this to not update the primary key because the INSERT failed.

To test this I inserted two rows, primary key was 1 and 2 respectively. I then attempted to insert the data from row two again. It failed due to the unique constraint. I then inserted another row with a unique value and the primary key jumped from 2 to 4, skipping the three that would have been used had the unique constraint not failed.

Is this expected behaviour for Postgres?

CodePudding user response：

That is normal behavior.

The documentation explains that:

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Such cases will leave unused “holes” in the sequence of assigned values. Thus, PostgreSQL sequence objects cannot be used to obtain “gapless” sequences.

For details and examples, you can read my blog.