If you’re getting an error that reads something like “nextval: reached minimum value of sequence “sequence1” (-3)” in PostgreSQL, it’s probably because you’re trying to generate a new value from a descending sequence, but the sequence has already reached its minimum value.

We have a number options when it comes to dealing with this issue.

Here are six ways to fix this issue:

• Change the minimum value of the sequence.
• Remove the existing MINVALUE from the sequence.
• Restart (or “reset”) the sequence.
• Change it to a cycling (or repeating) sequence.
• Change the sequence to an incrementing sequence.
• Create a new sequence with our desired settings.

The examples below demonstrate these six options.

Example of Error

Suppose we create the following sequence:

CREATE SEQUENCE Sequence1
    INCREMENT -1
    START -1
    MINVALUE -3;

This sequence has a very small range, so we can easily generate the error by calling the sequence more than three times.

Let’s generate the error:

SELECT 
    nextval('Sequence1'),
    nextval('Sequence1'),
    nextval('Sequence1'),
    nextval('Sequence1');

Result:

nextval: reached minimum value of sequence "sequence1" (-3)

Here, the first three calls to nextval() decremented the sequence from -1 to -3, but the next call resulted in the error.

Solution 1 – Change the MINVALUE

The first solution is to change the minimum value of the sequence object.

Example:

ALTER SEQUENCE Sequence1
    MINVALUE -1000;

This example changed the minimum value of the sequence to -1,000. Once that number is exceeded, we’ll get the same error again.

Solution 2 – Remove the MINVALUE

Unless we have reason to impose a minimum value, we might want to remove it altogether. We can do this by specifying NO MINVALUE:

ALTER SEQUENCE Sequence1
    NO MINVALUE;

This sets the minimum value to the default. The default for an descending sequence is the minimum value of the data type. I didn’t specify the data type and so it used bigint by default. This implicitly sets the minimum value to -9223372036854775808. The default MAXVALUE for a descending sequence is -1.

Solution 3 – Reset the Sequence

Another way of dealing with the error is to reset (or restart) the sequence.

Here’s one way to reset the sequence:

ALTER SEQUENCE Sequence1
    RESTART -1;

When we use the RESTART argument, we pass the value that we want the sequence to restart at. We can alternatively use RESTART WITH if we want to be more explicit (e.g. RESTART WITH -1).

We can also use the setval() function to reset the sequence:

SELECT setval('Sequence1', -1);

Result:

 setval 
--------
     -1

Solution 4 – Cycle the Sequence

Another way to go about it is to change the sequence to a repeating – or cycling – sequence.

We can do this with the CYCLE argument:

ALTER SEQUENCE Sequence1
    CYCLE;

Now when we call the sequence multiple times, it goes back to the start every time it exceeds the MINVALUE:

SELECT 
    nextval('Sequence1'),
    nextval('Sequence1'),
    nextval('Sequence1'),
    nextval('Sequence1');

Result:

 nextval | nextval | nextval | nextval 
---------+---------+---------+---------
      -1 |      -2 |      -3 |      -1

We can see that it got to its MINVALUE (-3), then started again at its START value.

Solution 5 – Change to an Incrementing Sequence

Another way to go about it is to change the sequence to an incrementing sequence. In this case, the sequence numbers will increase instead of decrease.

To do this, we can simply specify a positive increment value.

Example:

ALTER SEQUENCE Sequence1
    NO MAXVALUE
    INCREMENT 1;

When we do this, we may need to also specify a new MAXVALUE, or NO MAXVALUE like I did in this example. That’s because the default MAXVALUE for decrementing sequences is -1.

By specifying NO MAXVALUE, the maximum value for the sequence becomes the maximum value of the data type. In my case, this is 9223372036854775807. That’s because my sequence uses a bigint data type. It uses that type because bigint is the default data type, and I didn’t specify the data type when I created the sequence or when I altered it.

Now when we call our sequence multiple times, we can see that it’s an incrementing sequence:

SELECT 
    nextval('Sequence1'),
    nextval('Sequence1'),
    nextval('Sequence1'),
    nextval('Sequence1');

Result:

 nextval | nextval | nextval | nextval 
---------+---------+---------+---------
      -2 |      -1 |       0 |       1

Solution 6 – Create a New Sequence

If none of the previous solutions are suitable, we can always create a new sequence altogether. When we do this, we can specify our desired settings.

Example:

CREATE SEQUENCE Sequence2
    INCREMENT -1;

In this case I didn’t specify a MINVALUE, and therefore the sequence uses the minimum value of the data type, which in this case is -9223372036854775808 (due to the data type being the default bigint).

We can check the settings of our sequence like this:

SELECT 
    pc.relname,
    ps.seqstart,
    ps.seqincrement,
    ps.seqmax,
    ps.seqmin,
    ps.seqcache,
    ps.seqcycle
FROM pg_class pc
JOIN pg_sequence ps 
ON pc.oid = ps.seqrelid
WHERE pc.relname = 'sequence2';

Result:

  relname  | seqstart | seqincrement | seqmax |        seqmin        | seqcache | seqcycle 
-----------+----------+--------------+--------+----------------------+----------+----------
 sequence2 |       -1 |           -1 |     -1 | -9223372036854775808 |        1 | f

We can see that it uses the default MINVALUE (shown in the seqmin column) of -9223372036854775808.