Descending Sequences

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

The default increment for a Vertica sequence 1. But you can also create a sequence that has a negative increment so that you can have a count down.

Example:

dbadmin=> CREATE SEQUENCE up_seq;
CREATE SEQUENCE

dbadmin=> SELECT up_seq.NEXTVAL;
NEXTVAL
---------
       1
(1 row)

dbadmin=> SELECT up_seq.NEXTVAL;
NEXTVAL
---------
       2
(1 row)

dbadmin=> SELECT up_seq.NEXTVAL;
NEXTVAL
---------
       3
(1 row)

dbadmin=> CREATE SEQUENCE down_seq START WITH 3 INCREMENT BY -1;
CREATE SEQUENCE

dbadmin=> SELECT down_seq.NEXTVAL;
NEXTVAL
---------
       3
(1 row)

dbadmin=> SELECT down_seq.NEXTVAL;
NEXTVAL
---------
       2
(1 row)

dbadmin=> SELECT down_seq.NEXTVAL;
NEXTVAL
---------
       1
(1 row)

dbadmin=> SELECT down_seq.NEXTVAL;
ERROR 4705:  Sequence exceeded min value

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Sequences/Sequences.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATESEQUENCE.htm

Have fun!

Sign In or Register to comment.