Descending Sequences

Posted April 17, 2019 by Jim Knicely, Vertica Principal Solution Architect

Vintage businessman concept pointing on the wall wearing futuristic helmet at office
The default increment for a Vertica sequence is 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/latest/HTML/Content/Authoring/AdministratorsGuide/Sequences/Sequences.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATESEQUENCE.htm

Have fun!