Inserting values (generate_series) - how can I reuse/cycle the numbers, e.g, 1,2,3,1,2,3

postgresql generate-series

Rohita Khatiwada · Oct 17, 2011 · Viewed 8.8k times · Source

I am using generate_series to insert values in a table. And generate_series insert values as specified in its range.

For example: for the following query,

SELECT i AS id, i AS age, i AS house_number
INTO egg
FROM generate_Series(1,6) AS i;

the result we get is:

id  age  house_number
1   1    1
2   2    2
3   3    3
4   4    4
5   5    5
6   6    6

But my problem is, I want to insert only till number 3 in column "age" and then start from 0 after 3:

id  age  house_number
1   1    1
2   2    2
3   3    3
4   1    4
5   2    5
6   3    6

Is this possible? Are there some random functions in generate_series() which performs the same function?

Answer

You can use the modulo operation to cycle from 0 to n - 1 and add one:

SELECT i AS id, (i - 1) % 3 +1 AS age, i AS house_number
INTO egg
FROM generate_Series(1,6) AS i;