SEQUENCES
Displays information about the parameters specified for a sequence using the CREATE SEQUENCE statement.
Column Name | Data Type | Description |
---|---|---|
SEQUENCE_SCHEMA |
VARCHAR |
Schema in which the sequence was created. |
SEQUENCE_NAME |
VARCHAR |
Name of the sequence defined in the CREATE SEQUENCE statement. |
OWNER_NAME |
VARCHAR |
Name of the owner; for example, dbadmin. |
IDENTITY_TABLE_NAME |
VARCHAR |
If created by an |
SESSION_CACHE_COUNT |
INTEGER |
Count of values cached in a session. |
ALLOW_CYCLE |
BOOLEAN |
Values allowed to cycle when max/min is reached. See |
OUTPUT_ORDERED |
BOOLEAN |
Values guaranteed to be ordered (always false). |
INCREMENT_BY |
INTEGER |
Sequence values are incremented by this number (negative for reverse sequences). |
MINIMUM |
INTEGER |
Minimum value the sequence can generate. |
MAXIMUM |
INTEGER |
Maximum value the sequence can generate. |
CURRENT_VALUE |
INTEGER |
Specifies how many sequence numbers Vertica has distributed to the nodes in your cluster. Includes all nodes. |
SEQUENCE_SCHEMA_ID |
INTEGER |
A unique numeric ID assigned by the Vertica catalog, which identifies the schema. |
SEQUENCE_ID |
INTEGER |
A unique numeric ID assigned by the Vertica catalog, which identifies the sequence. |
OWNER_ID |
INTEGER |
A unique numeric ID assigned by the Vertica catalog, which identifies the user who created the sequence. |
IDENTITY_TABLE_ID |
INTEGER |
A unique numeric ID assigned by the Vertica catalog, which identifies the table to which the column belongs (if created by an |
Examples
Create a simple sequence:
=> CREATE SEQUENCE my_seq MAXVALUE 5000 START 150; CREATE SEQUENCE
Return information about the sequence you just created:
=> \x Expanded display is on. => SELECT * FROM sequences; -[ RECORD 1 ]-------+------------------ sequence_schema | public sequence_name | my_seq owner_name | dbadmin identity_table_name | session_cache_count | 250000 allow_cycle | f output_ordered | f increment_by | 1 minimum | 1 maximum | 5000 current_value | 149 sequence_schema_id | 45035996273704966 sequence_id | 45035996273844996 owner_id | 45035996273704962 identity_table_id | 0
An identity column is a sequence available only for numeric column types. To identify what column in a table, if any, is an identity column, search the COLUMNS
table to find the identity column in a table:
=> CREATE TABLE testid (c1 IDENTITY(1, 1, 1000), c2 INT); => \x Expanded display is on. => SELECT * FROM COLUMNS WHERE is_identity='t' AND table_name='testid'; -[ RECORD 1 ]------------+------------------ table_id | 45035996274150730 table_schema | public table_name | testid is_system_table | f column_name | c1 data_type | int data_type_id | 6 data_type_length | 8 character_maximum_length | numeric_precision | numeric_scale | datetime_precision | interval_precision | ordinal_position | 1 is_nullable | f column_default | is_identity | t
Use the SEQUENCES
table to get detailed information about the sequence in testid:
=> SELECT * FROM sequences WHERE identity_table_name='testid'; -[ RECORD 1 ]-------+-------------------- sequence_schema | public sequence_name | testid_c1_seq owner_name | dbadmin identity_table_name | testid session_cache_count | 1000 allow_cycle | f output_ordered | f increment_by | 1 minimum | 1 maximum | 9223372036854775807 current_value | 0 sequence_schema_id | 45035996273704976 sequence_id | 45035996274150770 owner_id | 45035996273704962 identity_table_id | 45035996274150768
Use the vsql command \ds
to return a list of sequences. The following results show the two sequences created in the preceding examples. If more sequences existed, the table would list them.
The CurrentValue of the new sequence is one less than the start number you specified in the CREATE SEQUENCE
and IDENTITY
commands, because you have not yet used NEXTVAL to instantiate the sequences to assign their cache or supply their first start values.
=> \ds List of Sequences -[ RECORD 1 ]+-------------------- Schema | public Sequence | my_seq CurrentValue | 149 IncrementBy | 1 Minimum | 1 Maximum | 5000 AllowCycle | f Comment | -[ RECORD 2 ]+-------------------- Schema | public Sequence | testid_c1_seq CurrentValue | 0 IncrementBy | 1 Minimum | 1 Maximum | 9223372036854775807 AllowCycle | f Comment |