The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Descending Sequences

Jim_KnicelyJim_Knicely - Select Field - Administrator

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.