Commands to reset primary key sequence of a table in PostgreSQL.
I was trying to insert a new record into an existing table in PostgreSQL using PHP. Then I met an error.
ERROR: duplicate key value violates unique constraint Detail: Key (id)=(15) already exists.
The reason for my failure is that primary key sequence of the PostgreSQL table is in chaos. So the new sequence integer conflicted with an existing key.
This issue can be fixed simply by resetting the table's primary key sequence. Say my database name is company, my table name is user and its primary key column is id.
Firstly, login PostgreSQL and connect to my database.
$ sudo -u postgres psql postgres postgres=# \c company
Now I can get the current 'next value' of primary key sequence in user table.
company=# SELECT nextval('user_id_seq'); nextval --------- 16 (1 row)
Furthermore, I can see the maximum id value of the user table.
company=# SELECT MAX(id) FROM user; max ----- 24 (1 row)
I will reset primary key sequence of the user table to the maximum id value.
company=# SELECT setval('user_id_seq', (SELECT MAX(id) FROM user));
This will solve the duplicate key value violates unique constraint error.