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

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

Here are six ways to fix this issue:

• Increase the maximum value of the sequence.
• Remove the existing MAXVALUE from the sequence.
• Restart (or “reset”) the sequence.
• Change it to a cycling (or repeating) sequence.
• Change the sequence to a decrementing 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
    START 1
    MAXVALUE 3;

This sequence has a very low MAXVALUE 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:

ERROR:  nextval: reached maximum value of sequence "sequence1" (3)

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

Solution 1 – Increase the MAXVALUE

The first solution is to increase the maximum value of the sequence object.

Example:

ALTER SEQUENCE Sequence1
    MAXVALUE 1000;

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

Solution 2 – Remove the MAXVALUE

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

ALTER SEQUENCE Sequence1
    NO MAXVALUE;

This sets the maximum value to the default. The default for an ascending sequence is the maximum value of the data type. I didn’t specify the data type and so it used bigint by default. This implicitly sets the maximum value to 9223372036854775807. 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 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 MAXVALUE:

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 MAXVALUE (3), then started again at its START value.

Solution 5 – Change to a Decrementing Sequence

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

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

Example:

ALTER SEQUENCE Sequence1
    NO MINVALUE
    INCREMENT -1;

When we do this, we may need to also specify a new MINVALUE or remove it altogether. In this example I removed it, and so the default MINVALUE is used, which is minimum value of the data type. In my case, this is -9223372036854775808. 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 a decrementing 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;

This example demonstrates the most concise way of creating a sequence. I didn’t specify any settings – all I did was provide the sequence name. Therefore, it uses the default settings. This includes the default MAXVALUE.

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 | 9223372036854775807 |      1 |        1 | f

We can see that it uses the default MAXVALUE (shown in the seqmax column) of 9223372036854775807.