How to Reset Primary Key Sequence in A PostgreSQL Table

By:   –  Last updated:   –  #postgresql

Code Theme [Dark]

Content Overview [Hide]

Commands to reset primary key sequence of a table in PostgreSQL.

1 Issue

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.

2 Reason

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.

3 Solution

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.