POSTGRES - prevent serial incrementation with ON CONFLICT DO NOTHING

Question

duplicate of this question

Say I have the following table things. I want unique names in the table so there are no duplicates. The process that inserts a thing shouldn't need to check if a thing with this name is already there.

CREATE TABLE things(
    id SMALLSERIAL PRIMARY KEY,
    name varchar UNIQUE
);

When I insert values like this it works. If 'desk' is already in things it won't be inserted.

INSERT INTO things (name)
VALUES ('desk')
ON CONFLICT DO NOTHING;

Tho only problem is ON CONFLICT DO NOTHING does not really do nothing. It still increments the sequence for the id field.

If this happens too often the id sequence eventually gets too big for the field type.

Is there a way to prevent this from happening?

Answer 1

Using insert ... on conflict, you can't prevent the serial to auto-increment on conflict. Postgres (just like other databases) does not guarantee sequential serials, as explained in the documentation:

Because smallserial, serial and bigserial are implemented using sequences, there may be "holes" or gaps in the sequence of values which appears in the column, even if no rows are ever deleted. A value allocated from the sequence is still "used up" 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.

If you are running a lots of insert that end up in conflict, one way to limit the bleeding would be to change the syntax to not exists:

insert into things (name)
select name
from (values ('desk')) v(name)
where not exists (select 1 from things t1 where t1.name = v.name)

Note that this still does not guarantee that serials will be sequential (refer to the above quote from the documentation).