Get the next value of a sequence

The syntax for NEXT VALUE is:

NEXT VALUE FOR <sequence name>

If there is no next value without overshooting the maxvalue for ascending sequences or undershooting the minvalue for descending sequences, the system does the following:

• For a noncycling sequence, the system displays an error.
• For a cycling sequence, the next value wraps to the minvalue for ascending sequences and wraps to maxvalue for descending sequences.

You cannot use NEXT VALUE in the following statements:

• CASE expressions
• WHERE clauses
• ORDER BY clauses
• Aggregate functions
• Window functions
• Grouped queries
• SELECT distinct

You can use the next value of a sequence of one precision (for example, bigint) to supply a value for a column of a different precision (such as smallint).

CREATE TABLE small_int_table (col1 smallint);
CREATE SEQUENCE bing_int_seq as bigint;
INSERT INTO small_int_table SELECT NEXT VALUE FOR big_int_seq;