When we create a sequence in PostgreSQL, we have the option of making it a non-repeating sequence or repeating sequence.
By “repeating” sequence, I mean that, once it reaches its maximum value (or minimum if it’s a descending sequence), it starts all over again, re-using numbers previously generated.
To create a repeating sequence, we use the CYCLE
keyword when creating the sequence. By default, it’s created as NO CYCLE
, but we can change this simply by specifying CYCLE
.
Example
Here’s an example of creating a repeating sequence:
CREATE SEQUENCE Sequence1
START 1
MAXVALUE 3
CYCLE;
This sequence will cycle between 1 and 3. Once it reaches 3 it will start again at 1, and so on.
Let’s see it in action:
SELECT nextval('Sequence1')
UNION ALL
SELECT nextval('Sequence1')
UNION ALL
SELECT nextval('Sequence1')
UNION ALL
SELECT nextval('Sequence1')
UNION ALL
SELECT nextval('Sequence1')
UNION ALL
SELECT nextval('Sequence1')
UNION ALL
SELECT nextval('Sequence1')
UNION ALL
SELECT nextval('Sequence1');
Result:
nextval --------- 1 2 3 1 2 3 1 2
Descending Sequences
The CYCLE
argument applies equally to descending sequences.
Example:
CREATE SEQUENCE Sequence2
START 0
INCREMENT -1
MINVALUE -3
MAXVALUE 0
CYCLE;
This sequence will cycle between 0 and -3. Once it reaches -3 it will start again at 0, and so on.
Let’s see it in action:
SELECT nextval('Sequence2')
UNION ALL
SELECT nextval('Sequence2')
UNION ALL
SELECT nextval('Sequence2')
UNION ALL
SELECT nextval('Sequence2')
UNION ALL
SELECT nextval('Sequence2')
UNION ALL
SELECT nextval('Sequence2')
UNION ALL
SELECT nextval('Sequence2')
UNION ALL
SELECT nextval('Sequence2');
Result:
nextval --------- 0 -1 -2 -3 0 -1 -2 -3 (8 rows)