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.