Why doesn't id take into account inserted rows?

Question

PostgreSQL

Greetings!

CREATE TABLE time_interval (
  id serial NOT NULL,
  name varchar(128) NOT NULL,
  CONSTRAINT time_interval_pkey PRIMARY KEY (id)
);

--запрос 1
insert into time_interval (id, name) values(1,'test1'),(2,'test2'),(3,'test3');

--запрос 2
insert into time_interval (name) values('test4'),('test5'),('test6');

query 1 inserts 3 rows with prepared IDs into the table.
and works successfully.

query 2 returns an error.

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "time_interval_pkey"
Detail: Key (id)=(1) already exists.

why is the id field not auto-incrementing in the second query, and how to fix it, how is it done in postgresql?

Answer 1

You don't have to do that. If you want to make entries with your ID, then it's better to put INT for example.
serial is an auto-increment for the table it describes. This function is stateful and has pointers such as *nextval()*.
If you call insert 2 times, the current pointer will be equal to two and after 2 more inserts already with your identifiers (3 and 4) the pointer will still be equal to two and the next time it will try to add id = 3
Autoincrement in order to exist so as not to worry about unique record identifier where it is not particularly needed.
In your case, if you made such a mistake, you already need to do RESTART with the last id