SEQUENCES

Displays information about sequences.

Displays information about sequences.

Column Name Data Type Description
SEQUENCE_SCHEMA VARCHAR Sequence schema
SEQUENCE_NAME VARCHAR

One of the following:

  • User-assigned name of a sequence created with CREATE SEQUENCE

  • Auto-generated name assigned by Vertica for the sequence of an IDENTITY table column

OWNER_NAME VARCHAR

One of the following:

  • Owner of the named sequence

  • Owner of the table where an IDENTITY column is defined

IDENTITY_TABLE_NAME VARCHAR Set only for IDENTITY column sequences, name of the column table
SESSION_CACHE_COUNT INTEGER Count of values cached in a session
ALLOW_CYCLE BOOLEAN Whether values cycle when a sequence reaches its minimum or maximum value, as set by CREATE SEQUENCE parameter CYCLE|NO CYCLE
OUTPUT_ORDERED BOOLEAN Values guaranteed to be ordered, always false
INCREMENT_BY INTEGER Value by which sequences are incremented or decremented
MINIMUM INTEGER Minimum value the sequence can generate.
MAXIMUM INTEGER Maximum value the sequence can generate.
CURRENT_VALUE INTEGER How many sequence numbers are distributed among all cluster nodes.
SEQUENCE_SCHEMA_ID INTEGER Unique numeric catalog ID of the sequence schema
SEQUENCE_ID INTEGER Unique numeric catalog ID of the sequence
OWNER_ID INTEGER Unique numeric catalog ID of the user who created the sequence
IDENTITY_TABLE_ID INTEGER Set only for IDENTITY column sequences, unique numeric catalog ID of the column table

Examples

Create a sequence:

=> CREATE SEQUENCE my_seq MAXVALUE 5000 START 150;
CREATE SEQUENCE

Return information about this sequence:

=> SELECT sequence_schema, sequence_name, owner_name, session_cache_count, increment_by, current_value FROM sequences;
 sequence_schema |      sequence_name       | owner_name | session_cache_count | increment_by | current_value
-----------------+--------------------------+------------+---------------------+--------------+--------------
 public          | my_seq                   | dbadmin    |              250000 |            1 |           149
(2 rows)

IDENTITY columns are sequences that are defined in a table's DDL. IDENTITY column values automatically increment as new rows are added. To identify IDENTITY columns and their tables, query the system table COLUMNS:

=> CREATE TABLE employees (employeeID IDENTITY, fname varchar(36), lname varchar(36));
CREATE TABLE
=> SELECT table_name, column_name, is_identity FROM columns WHERE is_identity = 't';
 table_name | column_name | is_identity
------------+-------------+-------------
 employees  | employeeID  | t
(1 row)

Query SEQUENCES to get detailed information about the IDENTITY column sequence in employees:

=> SELECT sequence_schema, sequence_name, identity_table_name, sequence_id FROM sequences
      WHERE identity_table_name ='employees';
 sequence_schema |      sequence_name       | identity_table_name |    sequence_id
-----------------+--------------------------+---------------------+-------------------
 public          | employees_employeeID_seq | employees           | 45035996273848816
(1 row)

Use the vsql command \ds to list all named and IDENTITY column sequences. The following results show the two sequences created previously:

=> \ds
                                                   List of Sequences
 Schema |         Sequence         | CurrentValue | IncrementBy | Minimum |       Maximum       | AllowCycle | Comment
--------+--------------------------+--------------+-------------+---------+---------------------+------------+---------
 public | employees_employeeID_seq |            0 |           1 |       1 | 9223372036854775807 | f          |
 public | my_seq                   |          149 |           1 |       1 |                5000 | f          |
(2 rows)