PostgreSQL: How to reset primary key sequence if it falls out of sync?



Once I was working with PostgreSQL 9.4, I got an error of primary key constraint while I was using the sequence that generates the next serial number. But the sequence implied in the serial data type returned a number that already exists in the primary key column.

Now the challenge was to set the sequence value correctly so that it can return the correct number. The simple solutions are described below.

Reset primary key sequence if you know the name of the sequence


The following query will resolve your problem.


SELECT setval('mytable_id_seq', max(id)) FROM mytable;


Here one thing may create a problem like if you don't know the sequence name then what you will do here.

Reset primary key sequence if you don't know the name of the sequence


Using pg_get_serial_sequence function you can get the name of the sequence applied on a primary key column. The following query will help you to reset your primary key sequence.


SELECT setval(pg_get_serial_sequence('mytable', 'id'), max(id)) FROM mytable;


Note: PostgreSQL 10 introduced a new feature to generate identity column, if you are using PostgreSQL 10 or above version then not need to use SERIAL or BIGSERIAL data type to generate an identity column.

No comments:

Post a Comment

Please do not enter any spam link in the comment box.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...