In case of inserted a fixed value into autoincrement how to automatically skip into the next sequence value without causing insert to have an error?

Question

In my postgresql 9.2 database I created the following table:

CREATE TABLE dummy(id SERIAL PRIMARY KEY,text TEXT);

And I inserted a record via:

INSERT INTO dummy(text) VALUES ("Hello Word");

Then I inserted a record like that:

INSERT INTO dummy(id,text) VALUES (2,'SAYONARA Word');

Afterwards when I tried to insert a record like that:

INSERT INTO dummy(text) VALUES ('<3 Word');

I get the following error:

ERROR: duplicate key value violates unique constraint "dummy_pkey" DETAIL: Key (id)=(2) already exists.

But when I reinsert the value:

INSERT INTO dummy(text) VALUES ('<3 Word');

No error is thrown.

So as I see the potgresql autoincrements a value for id via an internal counter regardless whether the value is sucessfully inserted or not. Also the error has occured because postgresql does not "punches holes" on exising counter id.

So how I can tell postgresql if the autoincrement value exists just to skip into the next one and avoid using the existent autoincrement value. (By existing I mean values that have already set as primary key).

Answer 1

You can use the following statement to synchronize the sequence with the current max value of the ID column:

select setval(pg_get_serial_sequence('dummy', 'id'), (select max(id) from dummy));

Online example: https://rextester.com/GMK2630

You can run this manually after you have inserted rows with explicitly supplied values.

---

Note that this can still result in a wrong sequence value if this is done concurrently from multiple transactions because a transaction does not see the values inserted (but not committed) by other transactions.

But I would strong recommend to simply let the default do its job, adjusting the sequence like that is more a hack, rather than good coding practice.