Creating and Using Named Sequences
You create a named sequence with CREATE SEQUENCE
. The statement requires only a sequence name; all other parameters are optional. To create a sequence, a user must have CREATE privileges on a schema that contains the sequence.
The following example creates an ascending named sequence, my_seq
, starting at the value 100:
=> CREATE SEQUENCE my_seq START 100; CREATE SEQUENCE
Incrementing and Decrementing a Sequence
When you create a named sequence object, you can also specify its increment or decrement value by setting its INCREMENT
parameter. If you omit this parameter, as in the previous example, the default is set to 1.
You increment or decrement a sequence by calling the function NEXTVAL
on it—either directly on the sequence itself, or indirectly by adding new rows to a table that references the sequence. When called for the first time on a new sequence, NEXTVAL
initializes the sequence to its start value. Vertica also creates a cache for the sequence. Subsequent NEXTVAL
calls on the sequence increment its value.
The following call to NEXTVAL
initializes the new my_seq
sequence to 100:
=> SELECT NEXTVAL('my_seq'); nextval --------- 100 (1 row)
Getting a Sequence's Current Value
You can obtain the current value of a sequence by calling CURRVAL
on it. For example:
=> SELECT CURRVAL('my_seq'); CURRVAL --------- 100 (1 row)
CURRVAL
returns an error if you call it on a new sequence that has not yet been initialized by NEXTVAL
, or an existing sequence that has not yet been accessed in a new session. For example:
=> CREATE SEQUENCE seq2; CREATE SEQUENCE => SELECT currval('seq2'); ERROR 4700: Sequence seq2 has not been accessed in the session
Referencing Sequences in Tables
A table can set the default values of any column to a named sequence. The table creator must have the following privileges: SELECT on the sequence, and USAGE on its schema.
In the following example, column id
gets its default values from named sequence my_seq
:
=> CREATE TABLE customer(id INTEGER DEFAULT my_seq.NEXTVAL, lname VARCHAR(25), fname VARCHAR(25), membership_card INTEGER );
For each row that you insert into table customer
, the sequence invokes the NEXTVAL
function to set the value of the id
column. For example:
=> INSERT INTO customer VALUES (default, 'Carr', 'Mary', 87432); => INSERT INTO customer VALUES (default, 'Diem', 'Nga', 87433); => COMMIT;
For each row, the insert operation invokes NEXTVAL
on the sequence my_seq
, which increments the sequence to 101 and 102, and sets the id
column to those values:
=> SELECT * FROM customer; id | lname | fname | membership_card -----+-------+-------+----------------- 101 | Carr | Mary | 87432 102 | Diem | Nga | 87433 (1 row)