NEXTVAL

Returns the next value in a sequence. NEXTVAL is used in INSERT, COPY, and SELECT statements to create unique column values.

Call NEXTVAL after creating a sequence to initialize the sequence with its default value. Thereafter, call NEXTVAL to increment the sequence value for ascending sequences, or decrement its value for descending sequences.

Behavior Type

Volatile

Syntax

NEXTVAL('[schema.]sequence-name')

Parameters

 

schema

Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example:

myschema.thisDbObject
sequence‑name

Identifies the target sequence.

Privileges

Examples

The following example creates an ascending sequence called my_seq, starting at 101:

=> CREATE SEQUENCE my_seq START 101;

The following command generates the first number in the sequence:

=> SELECT NEXTVAL('my_seq');
 nextval
---------
     101
(1 row)

The following command generates the next number in the sequence:

=> SELECT NEXTVAL('my_seq');
 nextval
---------
     102
(1 row)

The following command illustrates how NEXTVAL is evaluated on a per-row basis, so in this example, both calls to NEXTVAL yield the same result:

=> SELECT NEXTVAL('my_seq'), NEXTVAL('my_seq');
 nextval  | nextval
---------+---------
     103 |     103
(1 row)

The following example illustrates how the NEXTVAL is always evaluated first (and here, increments the my_seq sequence from its previous value), even when CURRVAL precedes NEXTVAL:

=> SELECT CURRVAL('my_seq'), NEXTVAL('my_seq');
 currval | nextval
---------+---------
     104 |     104
(1 row)

The following example shows how to use NEXTVAL in a table SELECT statement. Notice that the nextval column is incremented by 1 again:

=> SELECT NEXTVAL('my_seq'), product_description FROM product_dimension LIMIT 10;
 nextval |     product_description
---------+------------------------------
     105 | Brand #2 bagels
     106 | Brand #1 butter
     107 | Brand #6 chicken noodle soup
     108 | Brand #5 golf clubs
     109 | Brand #4 brandy
     110 | Brand #3 lamb
     111 | Brand #11 vanilla ice cream
     112 | Brand #10 ground beef
     113 | Brand #9 camera case
     114 | Brand #8 halibut
(10 rows)

See Also