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
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
- SELECT privilege on sequence
- USAGE privilege on sequence schema
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)