Postgres SERIAL add value after error INSERT command

Question

I created a table

CREATE TABLE street (
    id SERIAL PRIMARY KEY NOT NULL,
  street_name CHAR (30) NOT NULL,
  city_id INTEGER REFERENCES city,
  building_number CHAR(10));

after that I insert some data:

INSERT INTO street (street_name, city_id) VALUES ('Sumskaya', 1);

The data was added with id=1. Then I insert next data

INSERT INTO street (street_name, city_id) VALUES ('Sumskaya', 10);

and get the error

Key (city_id)=(10) is not present in table "city".

I changed my insert data

INSERT INTO street (street_name, city_id) VALUES ('Sumskaya', 2);

and get a row in the table with id = 3. Id = 2 is missing. Why serial assigned the value 3, not 2 and how to change it?

"*and how to change it?*" - you don't. This is how sequences work. The values of a _generated_ primary are totally meaningless. It does not matter if the next row gets the value 3, 42 or 9762938412942. The **only** thing such an ID has to be, is to be _unique_. — , Jul 17 '16 at 11:41
See also here: http://stackoverflow.com/q/9984196/330315 and here: http://stackoverflow.com/q/12423218/330315 and here: http://stackoverflow.com/q/3081899/330315 — , Jul 17 '16 at 11:46

score 2 · Answer 1 · answered Jul 17 '16 at 11:40

Serials internally use sequences. For concurrency reasons, sequences do not roll back. They just move forward. Imagine if you had two clients inserting at the same time.

Client 1 inserts a row, goes on to do some other work.
Client 2 inserts a row, goes on to do some other work
client 1 commits.
Client 1 inserts another row, goes on to do some other work
client 2 errors and rolls back.

We would expect values with an id of 1 and 3, and 2 just gets omitted.Anything else and we have problems.

If you truly need gapless nubering then you have to use a separate table and row locks, but then you cannot have concurrent clients inserting....

Postgres SERIAL add value after error INSERT command

1 Answers1