Vertica Sequence- How to Create and Use it?

  • Post author:
  • Post last modified:April 8, 2019
  • Post category:Vertica
  • Reading time:8 mins read

A sequence in Vertica is named object in an individual Vertica database schema, which provides unique incremental values. In most of the cases, sequence in Vertica are used to generate unique numbers that can use used as a primary key or surrogate key. In this article, we will check what is Vertica sequence, how to create and use it, types of sequences and restriction on using sequences.

Vertica Sequence Overview

A sequence output value is an integer. You can use this integer values wherever you would use numeric values. You can create individual named object or specify in DDL when creating tables.

Vertica supports user sequences for the four integer types: tinyint, smallint, integer, and bigint. Sequence accepts initial values, increment, minimum and maximum values. The sequences can start over again when end-point is reached. We will check all those details when discuss syntax of Vertica sequence.

When you generate the sequence number, the system increments the sequence independently of the transaction commit or rollback. Therefore, rollback doesn’t return the value back to sequence.

Related Articles:

Vertica Sequence Syntax

To create sequences use, CREATE SEQUENCE statement along with other options in any order.

CREATE SEQUENCE [[database.]schema.] <sequence name> ;

The create sequence statement accepts following options in any order:

START WITH <start value>
INCREMENT BY <increment>
no minvalue | minvalue <minimum value>
no maxvalue | maxvalue <maximum value>
cycle | no cycle

Meanings and default values of options:

  • The default start value is 1.
  • The default increment is 1.
  • The default minvalue si no minvalue which is same as start value 1.
  • The default maxvalue is no maxvalue and is the largest value a sequence can hold.
  • By default, sequences do not cycle.

Sample Sequence in Vertica

Create sequence that produces values between 1 and 500.

CREATE SEQUENCE vmart.testtdb.sequence1
START WITH 1 
increment by 1
minvalue 1 
maxvalue 500 
no cycle;

Getting Values from Sequences

After sequence is created in Vertica, there are two methods to get values from sequence. You can use these methods in your SELECT statement to get values from created sequence.

  • nextval – This statement returns next available value for named sequence.
  • currval – This statement returns current value of named sequence.

For example,

select nextval('vmart.testtdb.sequence1');

select currval('vmart.testtdb.sequence1');

Alter Sequence

You can alter a user named individual sequence by resetting any sequence options, including the name, changing schema, changing owner, etc.

To alter a sequence, use the ALTER SEQUENCE statement and specify the options in any order.

ALTER SEQUENCE [[database.]schema.] <sequence name> ;

Here options can be:

  • INCREMENT – Change increment value
  • MINVALUE/MAXVALUE – Change min and max value
  • RESTART – The sequence value on its next call to NEXTVAL.
  • CYCLE/NO CYCLE – Indicate what needs to be done after reached maxvalue.

For example,

ALTER SEQUENCE vmart.testtdb.sequence1 RESTART WITH 100;

Note that, ALTER SEQUENCE changes will take effect when you open new database session.

Types of Sequence in Vertica

Vertica database supports three types of sequences:

  • Named Sequence
  • AUTO_INCREMENT
  • IDENTITY

We have already seen what is named sequence in Vertica. Now let us check what are AUTO_INCREMENT and IDENTITY sequences?

AUTO_INCREMENT and IDENTITY Sequences

This sequence automatically increments the column value as new rows are added. You can use these sequences only with CREATE TABLE DDLs.

For examples, consider below DDL with auto_increment: 

CREATE TABLE vmart.testtdb.auto_increment_check(
     ID auto_increment(1,1), 
     lname VARCHAR(25),
     fname VARCHAR(25)
);

--Insert values to table:
INSERT INTO vmart.testtdb.auto_increment_check (lname, fname) VALUES ('Vithal', 'Samp');

--Check for inserted values. You can see ID is auto incremented.
select * from vmart.testtdb.auto_increment_check;

Consider below DDL with identity:

CREATE TABLE vmart.testtdb.identity_check(
     ID identity (1,1), 
     lname VARCHAR(25),
     fname VARCHAR(25)
);
--Insert values to table:
INSERT INTO vmart.testtdb. identity_check (lname, fname) VALUES ('Vithal', 'Samp');

--Check for inserted values. You can see ID is auto incremented.
select * from docker.ds_tbl_db. identity_check;

Restriction on use of Sequences

You cannot use NEXTVAL and CURRVAL in the following statements:

  • CASE expressions
  • WHERE clauses
  • ORDER BY clauses
  • Aggregate functions
  • Window functions
  • grouped queries
  • SELECT distinct

AUTO_INCREMENT and IDENTITY Restrictions

Below are restrictions on AUTO_INCREMENT and IDENTITY.

  • You cannot change the value of an AUTO_INCREMENT/IDENTITY column.
  • AUTO_INCREMENT/IDENTITY values are never rolled back.
  • A table can contain only one AUTO_INCREMENT/IDENTITY column.

Dropping a Sequence

To drop a sequence, use the DROP SEQUENCE statement and specify the sequence name.

DROP SEQUENCE [[database.]schema.] <sequence name>;

Although you can drop a sequence, remember that system locking occurs if you attempt to drop a sequence that is in use by a running query.

Hope this helps ?

Tags: ,