CURRVAL
Returns the last value across all nodes that was set by NEXTVAL
on this sequence in the current session. If NEXTVAL
was never called on this sequence since its creation, Vertica returns an error.
Behavior Type
Syntax
CURRVAL('[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 |
The target sequence |
Privileges
- SELECT privilege on sequence
- USAGE privilege on sequence schema
Examples
The following example creates an ascending sequence called sequential, starting at 101:
=> CREATE SEQUENCE seq2 START 101;
You can call CURRVAL
only after you initiate the sequence with NEXTVAL
; otherwise, Vertica returns an error:
=> SELECT CURRVAL('seq2'); ERROR: Sequence seq2 has not been accessed in the session
Use NEXTVAL
to generate the first number for this sequence:
=> SELECT NEXTVAL('seq2'); nextval --------- 101 (1 row)
Now you can use CURRVAL
to return the current number from this sequence:
=> SELECT CURRVAL('seq2'); currval --------- 101 (1 row)
The following command shows how to use CURRVAL
in a SELECT
statement:
=> CREATE TABLE customer3 ( lname VARCHAR(25), fname VARCHAR(25), membership_card INTEGER, ID INTEGER ); INSERT INTO customer3 VALUES ('Brown' ,'Sabra', 072753, CURRVAL('my_seq')); SELECT CURRVAL('seq2'), lname FROM customer3; CURRVAL | lname ---------+------- 101 | Brown (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 ---------+--------- 102 | 102 (1 row)