Prevent Primary Key increment when an INSERT statement fails in PostgreSQL

I have a simple table in my PostgreSQL database like this:

CREATE TABLE person_type
(
  id serial NOT NULL,
  name character(55) NOT NULL,
  CONSTRAINT person_type_pkey PRIMARY KEY (id),
  CONSTRAINT person_type_name_key UNIQUE (name)
)

As you can see the id is automatically incremented and the name must be unique. Every time I run an INSERT sql statements, even when it fails, the id is incremented by one.

How can I prevent this? In order that each time a row is inserted successfully, the primary keys are correlative

this is exactly what you are facing:

test=# CREATE SEQUENCE seq_a;
CREATE SEQUENCE
test=# SELECT nextval('seq_a');
 nextval 
---------
       1
(1 row)

test=# BEGIN;
BEGIN
test=# SELECT nextval('seq_a');
 nextval 
---------
       2
(1 row)

test=# ROLLBACK;
ROLLBACK
test=# SELECT nextval('seq_a');
 nextval 
---------
       3
(1 row)

a sequence makes sure that values are ascending BUT it does not make sure that it does not contain gaps. it is also important to notice that a sequence cannot be rollbacked. you cannot have strictly ascending and gap-free at the same time as it would not work with a mix of long and short transactions.
therefore a sequence should never be used for an invoice-id and so on.

StackExchange Database Administrators Q#83350, answer score: 6