In this article, I will let you know that how to resolve primary key sequence issue in PostgreSQL. This issue will only come when your application care about gaps in sequence.
Gaps in sequence is normal when you perform some rollback transaction after some errors
Below is the formatted error message that you can get while inserting new rows:
ERROR: duplicate key value violates unique constraint "table_name_pkey"
DETAIL: Key (id)=(x) already exists.
If you get this error then here is the solution for you :
SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);
SELECT nextval('your_table_id_seq'); this code will let you know the higher than the last result.
If you want to defend against concurrent inserts while you update the sequence then lock the table with below line of code.
LOCK TABLE your_table IN EXCLUSIVE MODE;
It will keep concurrent transactions from writing a higher number.