That's expected. You can see the note here:
https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL
Note
Because smallserial, serial and bigserial are implemented using sequences, there may be "holes" or gaps in the sequence
ofvalues which appears in the column, even if no rows are ever deleted. A value allocated from the sequence is still
"usedup" even if a row containing that value is never successfully inserted into the table column. This may happen, for
example,if the inserting transaction rolls back. See nextval() in Section 9.16 for details.
-----Original Message-----
From: PG Bug reporting form <noreply@postgresql.org>
Sent: Wednesday, July 31, 2019 7:30 AM
To: pgsql-bugs@lists.postgresql.org
Cc: muhammadalinazarov@gmail.com
Subject: BUG #15935: Auto increment column changes on error while inserting (violating unique constraint)
The following bug has been logged on the website:
Bug reference: 15935
Logged by: Muhammadali Nazarov
Email address: muhammadalinazarov@gmail.com
PostgreSQL version: 11.1
Operating system: Ubuntu 16.04.3 LTS (GNU/Linux 4.4.0-96-generic x86
Description:
I have seen one interesting behavior.
Let me show on example.
create table tmp (
id serial,
user_id int unique not null);
insert into tmp(user_id)
select 1;
our id => 1, autoincrement value 2;
insert into tmp(user_id)
select 1;
violating unique constraint, autoincrement value 3;
insert into tmp(user_id)
select 2;
select id, user_id
from tmp;
id | user_id
----+---------
1 | 1
3 | 2
Is this a bug or no?
Thank you for your attention.
