The Vertica Forum is getting a makeover! The site will be undergoing maintenance from Tuesday 8/13 to Friday 8/16. We appreciate your patience and cooperation during this time.

Descending Sequences

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.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.