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. — user330315
– user330315, Commented Jul 17, 2016 at 11:41
See also here: stackoverflow.com/q/9984196/330315 and here: stackoverflow.com/q/12423218/330315 and here: stackoverflow.com/q/3081899/330315 — user330315
– user330315, Commented Jul 17, 2016 at 11:46

Chris Travers · Accepted Answer · 2016-07-17 11:40:05Z

3

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

answered Jul 17, 2016 at 11:40

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres SERIAL add value after error INSERT command

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related