If you’re getting an error that reads something like “START value (101) cannot be greater than MAXVALUE (100)” in PostgreSQL when you’re trying to create a sequence, it’s probably because your sequence’s maximum possible value is lower than the start value.

To fix this issue, change either the start value or maximum value so that the maximum value is greater than the start value.

Example of Error

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

CREATE SEQUENCE Sequence1
    INCREMENT 1
    START 101
    MAXVALUE 100;

Result:

ERROR:  START value (101) cannot be greater than MAXVALUE (100)

We can get the same error even when we don’t explicitly specify a MAXVALUE. For example, we can get the same error if we use a START value that’s greater than the maximum value for the data type.

Here’s an example of what I mean:

CREATE SEQUENCE Sequence2 AS smallint
    START 40000;

Result:

ERROR:  START value (40000) cannot be greater than MAXVALUE (32767)

Same error (except that the numbers are different).

In this case I specified that the sequence should use a data type of smallint, but I specified that the sequence should start at 40000, which is higher than the maximum value for the smallint data type (which is 32767).

Solution 1

Regardless of how we get this error, the solution is the same – to ensure that our MAXVALUE is greater than the START value. However, there are a couple of ways to go about this.

The first solution is to increase the MAXVALUE, decrease the START value, or do both.

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

CREATE SEQUENCE Sequence1
    INCREMENT 1
    START 101
    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 START value. Or we could have done both – increased the MAXVALUE while decreasing the START value.

Solution 2

Another option is to change the data type of the sequence.

In the second example, I specified a data type of smallint, but this was insufficient for the START value. Therefore, we can change the data type to either integer or bigint in order to provide more headroom for the sequence.

Here’s an example of fixing the second example by changing the data type:

CREATE SEQUENCE Sequence2 AS integer
    START 40000;

Result:

Commands completed successfully

This time the command ran successfully.

I could also have removed the data type altogether:

CREATE SEQUENCE Sequence2
    START 40000;

Result:

Commands completed successfully

Doing this creates a sequence with the bigint data type, which has a much greater range than the integer type (but also uses more storage).