When should I use CYCLE in a sequence?

Question

I'm using sequences in a PostgreSQL database to insert rows into tables.

When creating the sequences I have never used the CYCLE option on them. I mean they can generate pretty big numbers (in the order of 2^63 as far as I remeber) and I don't really see why I would like a sequence to go back to zero. So my question is:

When should I use CYCLE while creating a sequence?

Do you have an example where it makes sense?

For example when you want values 1 to 100, and then start over. — jarlh, Apr 20 '18 at 14:39
Ah... not as a primary key, but for other uses, such as a "bingo" game or "ticket" printing, etc. that use small numbers. — The Impaler, Apr 20 '18 at 14:43

score 1 · Answer 1 · answered Apr 20 '18 at 17:50

It seems a sequence can use CYCLE for other purposes rather than for primary key generation.

This is, in scenarios where the uniqueness of its value is not required; actually is quite the opposite, when the values are expected to cycle back and repeat themselves after some time.

For example:

When generating numbers that must return to the initial value and repeat themselves at some point, for any reason (e.g. implementing a "Bingo" game).
When the sequence is a temporary identifier that will last for a short period of time and will be unique during its life.
When the field is small -- or can accept a limited number of values -- and it doesn't matter if they repeat themselves.
When there is another field in the entity that will identify it, and the sequence value is used for something else.
When an entity has a composite unique key and the sequence value is only a part of it.
When using the sequence value to generate uniform distribution of values on a big set, though this is hardly a random assignation of values.
Any other cyclic number generation.

When should I use CYCLE in a sequence?

1 Answers1