SEQUENCES

Displays information about sequences.

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.
`IDENTITY_TABLE_NAME`	VARCHAR	If created by an AUTO_INCREMENT or IDENTITY column, the name of its table.
`SESSION_CACHE_COUNT`	INTEGER	Count of values cached in a session.
`ALLOW_CYCLE`	BOOLEAN	Values allowed to cycle when a sequence reaches its minimum or maximum value. See `CYCLE \| NO CYCLE` parameter in CREATE SEQUENCE.
`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 `auto_increment` or `identity` column).

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      |