0

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).

  • 2
    Simply do NOT insert the values specified directly. If you need to do it manually than re-init autoincrement with the value above current maximal one. – Akina Jul 22 '19 at 9:45
  • 2
    The "autoincrement" is only triggered when you do not specify a value. If you do specify an explicit value, Postgres assumes you know what you are doing and will not increment the sequence that's behind the serial column. – a_horse_with_no_name Jul 22 '19 at 10:06
  • @Akina Is there a way to automatically re-init the counter in case of conflict for example via a pre-insert hook? – Dimitrios Desyllas Jul 22 '19 at 10:20
  • 1
    No way in a query form. Dup error will fail the whole query. – Akina Jul 22 '19 at 10:34
  • Unrelated, but: Postgres 9.2 is no longer supported you should plan an upgrade to the current version as soon as possible. – a_horse_with_no_name Jul 22 '19 at 12:55
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.