I have a table called person with primary key on id;

I am trying to insert into this table with:

insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Ellissa', 'Gordge', '[email protected]', 'Male', '2022-03-19', 'Fiji');

There should not be any ID constraint which are being violated since it is a BIGSERIAL yet I am getting this:

It says Key id=(8) already exists and it is incrementing on each attempt to run this command. How can ID already exist? And why is it not incrementing from the bottom of the list?

If i specify the id in the insert statement, with a number which i know is unique it works. I just don't understand why is it not doing it automatically since I am using BIGSERIAL.

CodePudding user response：

Your sequence apparently is out of sync with the values in the column. This can happen when someone did INSERT INTO person(id, …) VALUES (8, …) (or maybe a csv COPY import, or anything else that did provide values for the id column instead of using the default), or when someone did reset the sequence of having inserted data.

You can alter the sequence to fix this:

ALTER SEQUENCE person_id_seq RESTART WITH (SELECT MAX(id) 1 FROM person);

You can set the sequence value to fix this:

SELECT setval('person_id_seq', MAX(id) 1) FROM person;

Also notice that it is recommended to use an identity column rather than a serial one to avoid this kind of problem.

CodePudding user response：

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;

This should kickstart your sequence table back in sync, which should fix everything. Make sure to change 'table_name' to the actual name. Cheers!