How to Reset Auto Increment Counter in PostgreSQL?

PostgreSQL is a powerful, open-source, object-relational database management system (DBMS) developed by a vibrant community. One common requirement for database administrators is resetting the auto-increment counter for primary key sequences. In PostgreSQL, this process is crucial for ensuring the orderly assignment of unique identifiers, which helps maintain data integrity and database optimization.

In this article, we will explain how to reset the auto-increment counter in PostgreSQL using different approaches, including the ALTER SEQUENCE command and the SETVAL function. We’ll provide detailed examples for each method.

How to Reset the Primary Key Sequence ID in PostgreSQL?

In PostgreSQL, an auto-increment counter is commonly implemented using a sequence object. Sequences are database objects designed to generate unique sequential numbers, which are often used as primary key values in database tables. This auto-increment functionality is typically achieved with the SERIAL data type.

1. Using the ALTER SEQUENCE command
   
2. Using the TRUNCATE command

Creating a Sample Table

Before we delve deeper into the queries, let's create the table courses in gfg database and insert some sample values in the table. The following code creates the Sample Table and inserts the entries in the table.

Query:

CREATE DATABASE gfg;
 // Create a table
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(100),
    fees DECIMAL(10, 2),
    instructor VARCHAR(100)
);
// Insert some sample data
INSERT INTO courses (course_id,course_name, fees, instructor) 
VALUES 
    (1,'Java', 10000, 'Akhil'),
    (2,'Python', 15000, 'Jacky'),
    (3,'C++', 20000, 'Sourav');

Output

1. Using the ALTER SEQUENCE command

The ALTER SEQUENCE command in PostgreSQL is used to change and modify the properties of a created sequence. In PostgresSQL, Sequences are database objects that mainly generate unique sequential numbers, & generated Id's are used as primary key values in Database operations.

Syntax:

    ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
        [ START [ WITH ] start ]
        [ MINVALUE minvalue | NO MINVALUE ]
        [ MAXVALUE maxvalue | NO MAXVALUE ]
        [ CACHE ]
        [ CYCLE | NO CYCLE ]
        [ OWNED BY { table_name.column_name | NONE } ];

• name: is the name of the sequence object to be altered.
• increment: is the increment value for the sequence. If omitted, the increment value is 1.
• start: is the start value for the sequence. If omitted, the start value is the current value of the sequence.
• minvalue and maxvalue: specify the minimum and maximum values for the sequence, respectively. If omitted, the minimum and maximum values are determined by the integer, bigint, or smallint data type of the sequence.
• cache: specifies the cache size for the sequence. If omitted, the cache size is set to the default value.
• cycle: specifies that the sequence should cycle after reaching its maximum value. If omitted, the sequence does not cycle.
• owned by: specifies the table and column that own the sequence. If omitted, the sequence is owned by no table or column

Example

The following query reset the sequence to start from the next value after the maximum course_id. Assuming the sequence name is courses_course_id_seq and returns the result as before:

        ALTER SEQUENCE courses_course_id_seq RESTART WITH 5;

The above query, resets the auto-increment sequence for the **course_id** column in the **courses** table to start from 5,

Output

2. Using SETVAL and pg_get_serial_sequence

The setval() function in PostgreSQL is used to set the value of a sequence.

Syntax:

    SETVAL(sequence_name, next_value)

Key Terms

• sequence_name: is the name of the sequence you want to set the value for.
  
• next_value: is the new value you want to set the sequence to.

Example

The following query reset the primary key sequence ID in PostgreSQL, you can use the SETVAL function along with pg_get_serial_sequence.

Query:

        -- Find the name of the sequence
        SELECT pg_get_serial_sequence('courses', 'course_id');
        -- Reset the sequence
        SELECT setval('courses_course_id_seq', (SELECT MAX(course_id)+1 FROM courses));

Output

Explanation:

In the above query, SELECT pg_get_serial_sequence('courses', 'course_id'); retrieves the name of the sequence associated with the course_id column in the courses table, and the second line SELECT setval('courses_course_id_seq', (SELECT MAX(course_id)+1 FROM courses)); resets the courses_course_id_seq sequence to start from the next value after the current maximum course_id in the courses table.

Conclusion

In summary, resetting the auto-increment counter in PostgreSQL is essential for managing primary key sequences effectively. We covered two primary methods: using the ALTER SEQUENCE command and the SETVAL function along with pg_get_serial_sequence. Both methods allow for efficient management of sequence values, ensuring data integrity and smooth operations in your PostgreSQL databases.