How to Create a Repeating Sequence in PostgreSQL

by

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)