Fix “MINVALUE (…) must be less than MAXVALUE (…)” When Creating a Sequence in PostgreSQL

If you’re getting an error that reads something like “MINVALUE (1) must be less than MAXVALUE (1)” in PostgreSQL when you’re trying to create a sequence, it’s probably because your sequence’s minimum possible value is higher than the maximum value.

To fix this issue, be sure that the sequence’s maximum value is greater than the minimum value.

Example of Error

Here’s an example of code that produces the error:

CREATE SEQUENCE Sequence1
    MINVALUE 1
    MAXVALUE 1;

Result:

ERROR:  MINVALUE (1) must be less than MAXVALUE (1)

We can get the same error even when we don’t explicitly specify a MAXVALUE.

Example:

CREATE SEQUENCE Sequence1
    INCREMENT -1
    MINVALUE 1;

Result:

ERROR:  MINVALUE (1) must be less than MAXVALUE (-1)

In this case I tried to create a descending sequence. I specified a MINVALUE but not a MAXVALUE. In this case I specified a MINVALUE that’s lower than what the default MAXVALUE would be set to, and so I received the error.

The error message tells us that the MAXVALUE is set to -1. In this case it’s because I didn’t specify a MAXVALUE value, and therefore it used the default value of -1. This is also what the START value would default to.

Solution

One way to deal with this issue is to increase the MAXVALUE, decrease the MINVALUE value, or do both.

Here’s an example of fixing the first example by increasing the MAXVALUE:

CREATE SEQUENCE Sequence1
    MINVALUE 1
    MAXVALUE 10000000000;

Result:

Commands completed successfully

In this case I increased the MAXVALUE to a much greater amount. The code ran without error, and my client told me that the command completed successfully.

Alternatively, we could have reduced the MINVALUE value. Or we could have done both – increased the MAXVALUE while decreasing the MINVALUE value.

The Descending Sequence

Regarding our second example, we tried to create a descending sequence and we didn’t specify a MAXVALUE value. This caused the MAXVALUE value to be set to the default value of -1. For descending sequences, the default START value is the MAXVALUE value.

In other words, if we don’t specify a START value, then it will start at the MAXVALUE amount. And if we don’t specify a MAXVALUE, then it is automatically set at -1 for descending sequences.

So, we can fix the second example by specifying a MAXVALUE value and ensuring that it’s greater than the MINVALUE:

CREATE SEQUENCE Sequence1
    INCREMENT -1
    MINVALUE 1
    MAXVALUE 10000000000;

Result:

Commands completed successfully

This time the command ran successfully.