How to Reset a Sequence in PostgreSQL

Using the PostgreSQL sequence is an effective way of working with an auto-increment value, especially for primary keys. When the start value is not defined, the auto-increment for INT will begin at 0. However, you can specify what starting value to use for the sequence in your auto-increment column.

Moreover, if you get a situation that requires you to change the next value in the sequence, you can quickly reset the sequence and specify what value to start with for the next entry in the table. All the details you should know regarding how to reset a sequence in PostgreSQL are detailed in this post. Take a look!

Example on How to Reset a Sequence in PostgreSQL

With an INT column, you can create an auto-increment sequence and use it as the primary key for your table. That way, you won’t need to enter its values when running an INSERT query since the integer values will get generated automatically. With the sequence is created, the ALTER SEQUENCE command lets you restart the sequence and specify a new starting value for the new entries.

For this example, we will work with the following “user_details” table. Our focus is the “user_id” column which is an auto-increment sequence starting at 100.

To list the different sequences in your database, run the following command:

For our case, we target the “user_id” sequence.

To get more details on the sequence, run the “select” query as demonstrated in the following:

We can see that the last value in the existing sequence is 104. We reset this value and define a new value that the new entries will have.

To reset the PostgreSQL sequence, use the following command:

For our case, let’s say that we want to restart the sequence and start the new entries at 20. After running the following command, we get an output, as shown in the following, which confirms that the sequence has been altered.

If we rerun the earlier command to get the details of our sequence using the SELECT statement, we can see that the new “last_value” for this case is the value that we specified with the ALTER command.

Let’s insert a new entry into our table to see what happens. The following image shows the INSERT query and the results that we get after running the SELECT statement. Notice how the new entry has its “user_id” set to 20. All the new entries that we make will have their “user_id” generated based on the last value which is 20.

If we make another input, its “user_id” will be 21 and the sequence will proceed using the same logic. That’s how you reset a sequence in PostgreSQL. Note that the “reset” command doesn’t affect the existing records. It only defines the new starting value for the entries that you make after resetting the sequence.

Conclusion

With PostgreSQL, having a way to auto-generate the integer values in your primary key column simplifies things. When you have an existing sequence, you can use the ALTER SEQUENCE
command with the RESTART WITH option to specify which sequence you want to reset and what value to set as the last value in the sequence. That way, new entries will have the integer value generated based on the new last value. Refer to the presented example to understand how resetting a PostgreSQL sequence works.