How to Reset PostgreSQL Primary Key Sequence When it Falls Out of Sync?

PostgreSQL, often referred to as Postgres is a powerful, open-source object-relational database management system (ORDBMS). It is widely recognized for its robustness, scalability, and capability to manage large volumes of data. PostgreSQL has been actively developed for over 30 years, with its origins tracing back to the POSTGRES project at the University of California, Berkeley.

How to Reset the Primary Key Sequence ID in PostgreSQL

Let's create the table first in which we will perform our queries. There is a table named Programming which has 2 columns named id and language in which id is the serial primary key.

Step 1: Create the Table

--create table
CREATE TABLE Programming (
    id INT AUTO_INCREMENT PRIMARY KEY,
    language VARCHAR(50) NOT NULL
);

Step 2: Insert Data into the Table

-- Insert multiple values into the language column
INSERT INTO Programming (language) VALUES ('C'), ('C++'), ('Java');

-- Insert a specific ID and language value
INSERT INTO Programming (id, language) VALUES (5, 'Python');

Our table will look something like this.

Now, If I want to add data into Programming Table then I must reset the primary key sequence otherwise it will give me the duplicate primary key error. So we have to reset our primary key sequence. For that we will use 'pg_get_serial_sequence'. We can reset our primary key sequence using following steps.

Step 3: Resetting the Primary Key Sequence

Step 3.1: Identify the Sequence Name

First, find the name of the sequence associated with the primary key using the pg_get_serial_sequence() function.

Syntax:

SELECT pg_get_serial_sequence('table_name', 'column_name');

Example

SELECT pg_get_serial_sequence('table_name', 'column_name');

Output:

 pg_get_serial_sequence
--------------------------
 public.programming_id_seq
(1 row)

This will show the name of the serial sequence. This name will be needed in the 'setval' function. So using pg_get_serial_sequence() function we can get the name of serial sequence.

Step 3.2: Check the Current Max ID in Your Table

Next, determine the Maximum ID currently in use in the table. This will help in setting the sequence to the correct next value. We will use MAX() function for this.

Syntax:

SELECT MAX(column_name) FROM table_name;

Example

SELECT MAX(id) FROM Programming;

Output:

 max
-----
   5
(1 row)

Using MAX() function we can get the maximum value of the current value of the corresponding column. In our table , maximum value of the the id is 5.

Step 3.3: Reset the Sequence

Once you have the maximum value, reset the sequence using the setval() function. The goal is to set the sequence’s next value to the max ID found plus one.

Syntax:

SELECT setval('sequence_name', (SELECT MAX(column_name) FROM table_name) + 1);

Example

SELECT setval('programming_id_seq', (SELECT MAX(id) FROM Programming) + 1);

Output:

 setval
--------
      6
(1 row)

Alternatively, if you are sure that the current max ID indeed reflects the next needed primary key (i.e., there are no gaps in your primary key column), you can set the sequence to generate IDs starting with the next value immediately following the current max ID.

Syntax:

SELECT setval('sequence_name', COALESCE((SELECT MAX(column_name)+1 FROM table_name), 1), false);

Here, COALESCE is used to handle cases where the table might be empty (i.e., MAX(column_name) would return null). The sequence will start at 1 in such cases.

Advantages of Resetting the Primary Key Sequence

1. Prevents Duplicate Key Errors: Ensures that new inserts do not fail due to primary key conflicts.
   
2. Maintains Data Integrity: Keeps the sequence in sync with the actual data, maintaining the integrity of the primary key.
   
3. Simplifies Data Management: Reduces the complexity of manually tracking and setting primary key values, especially in large databases.
   
4. Facilitates Smooth Data Operations: Allows seamless data insertion operations without interruptions or errors related to primary key sequence.
   
5. Optimizes Performance: Ensures efficient use of the primary key sequence, avoiding gaps and fragmentation that could affect performance.

Conclusion

Using the sequence of some function, We can reset the sequence if it falls out of sync. The functions we used are pg_get_serial_sequence() using which we can get the name of the sequence, MAX() using which we can get the maximum value from the column, setval() using which we will reset the sequence to the perticular number.