In PostgreSQL, a commonly encountered error is the “nextval: reached minimum value” error, which occurs when working with descending sequences. Users encounter this error when a sequence reaches its minimum value and they attempt to generate a new value. Postgres provides several solutions for this issue, including resetting the sequence, modifying the minimum value of the sequence, repeating the sequence, and more.

This write-up illustrates various fixes for the “nextval reached minimum value” error in PostgreSQL.

How to Fix the “nextval: reached minimum value of sequence” Error in Postgres?

We have already created a sequence named “decrement_seq” with the following details:

\d decrement_seq;

In the following snippet, we invoked the nextval() function six times (more than the specified minimum value):

SELECT nextval('decrement_seq'),
nextval('decrement_seq'),
nextval('decrement_seq'),
nextval('decrement_seq'),
nextval('decrement_seq'),
nextval('decrement_seq');

Upon doing so, we will be prompted with the following error:

The following methods can be utilized in Postgres to rectify the “nextval: reached a minimum value” error:

• Method 1: Create a Repeating Sequence
• Method 2: Change the Sequence Minimum Value
• Method 3: Use Default Minimum Value
• Method 4: RESTART the Sequence

Method 1: Create a Repeating Sequence

Use the cycle option to create a repeating sequence. By doing so, the sequence will restart once it reaches the minimum limit:

ALTER SEQUENCE decrement_seq
CYCLE;

Type the below-given command to see the modified structure of the “decrement_seq”:

\d decrement_seq;

The below snippet demonstrates that the “cycles” option has been enabled, which means that the sequence will restart whenever it reaches the specified minimum value:

For more clarity, you can execute the nextval() function once more:

SELECT nextval('decrement_seq');

Now, the sequence will reset to the beginning whenever it exceeds the MINVALUE:

Method 2: Change the Sequence Minimum Value

Changing the minimum value of the sequence will also fix the said error:

ALTER SEQUENCE decrement_seq
MINVALUE -50;

The following snippet clarifies that the sequence has been successfully altered:

Method 3: Remove the Minimum Value

Specify the NO MINVALUE option to set the default minimum value:

ALTER SEQUENCE decrement_seq
NO MINVALUE;

The provided screenshot indicates that the default minimum value has been set as the minimum value for the “decrement_seq” sequence:

Method 4: RESTART the Sequence

Resetting the sequence value means starting the sequence again from the beginning. For this purpose, you can use the ALTER SEQUENCE command with the RESTART option:

ALTER SEQUENCE decrement_seq
RESTART;

The below screenshot proves that the sequence has been successfully altered:

That’s all about fixing the “reached minimum value of sequence” error.

Conclusion

In PostgreSQL, encountering the "nextval: reached minimum value" error is a common occurrence when a sequence reaches its minimum value, and the user still tries to generate a new value. To fix this issue, various solutions are available in Postgres, including the "Cycle" option, changing the minimum value, removing the MINVALUE option, and utilizing the RESTART option. This post has illustrated different approaches to fix the "nextval: reached minimum value" error in PostgreSQL.