postgresqlsql-insertgenerate-series

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

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?

Solution

  • 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;