How to Reset Auto Increment Counter in PostgreSQL?

PostgreSQL is a powerful, open-source, object-relational database management system (DBMS) developed by a community of contributors. In PostgreSQL, a counter is a type of variable that is used to keep track of a sequence of numbers. Counters used in databases to assign unique identifiers to new rows of data as they are inserted. In this article, we will explore how to reset the auto-increment counter in PostgreSQL? with the help of different approaches & we will also see examples of each approach.

How to Reset the Primary Key Sequence ID in PostgreSQL?

Resetting the primary key sequence ID in PostgreSQL ensures the orderly assignment of unique identifiers. Methods like the ALTER SEQUENCE command and SETVAL function facilitate this, ensuring database integrity and optimization. These techniques are vital for maintaining database consistency and efficiency.

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

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.

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:

DATA

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:

ALTER_SEQUENCE

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)

• 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.

        -- 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));

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.

Output:

SETVAl

Conclusion

Overall, we covered process of How to Reset the Primary Key Sequence ID in PostgreSQL. We already discuss, different approaches first Using the ALTER SEQUENCE  command. We understood the example of the ALTER SEQUENCE  command. We later looked at Using SETVAL and pg_get_serial_sequence functions and understood the ease it provide.