Altering the Next Serial Value

You can use the MODIFY clause to reset the next value of a SERIAL, BIGSERIAL, or SERIAL8 column. You cannot set the next value below the current maximum value in the column because that action can cause the database server to generate duplicate numbers. You can set the next value, however, to any value higher than the current maximum, which creates a gap in the series of values.

If the new serial value that you specify is less than the current maximum value in the serial column, the maximum value is not altered. If the maximum value is less than what you specify, the next serial number will be what you specify. The next serial value is not equivalent to one greater than the maximum serial value in the column in two situations:
  • There are no rows in the table, and an initial serial value was specified when the table was created (or by a previous ALTER TABLE statement).
  • There are rows in the table, but the next serial value was modified by a previous ALTER TABLE statement.
The following example sets the next serial value to 1000: 
ALTER TABLE my_table MODIFY (serial_num SERIAL (1000));

As an alternative, you can use the INSERT statement to create a gap in the series of serial values in the column. For more information, see Inserting Values into Serial Columns.