The following bug has been logged on the website:
Bug reference: 14126
Logged by: Casey Wireman
Email address: cwire4@gmail.com
PostgreSQL version: 9.5.2
Operating system: Windows 7
Description:
It's unclear to me if this is desired behavior or not, but when doing the
following:
insert into table(column) values (value) on conflict DO NOTHING
the first time a new entry is inserted it works as expected and
autoincrements the serial primary key as normal, however if I try to insert
this again the on conflict correctly detects that the value already exists,
but internally seems to autoincrement the serial key as many times as you
try to do the failed insert since on the next insert of a new value the key
value is
(previous successful key value + number of failed inserts)
It seems to me that intuitively the key should only increment on a
successful insert, not just an insert statement being executed. For one,
this unnecessarily reduces the keyspace available for the column.