Insertion of values ​​(generate_series) - how can I reuse / loop numbers, e.g. 1,2,3,1,2,3

Question

I use generate_series to insert values ​​into a table. And generate_series insert the values ​​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; 

we get the result:

 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 that I want to insert only to number 3 in the "age" column, 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 it possible? Are there some random functions in generate_series() that perform the same function?

Answer 1

You can use modulo operation for a loop 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; 

Answer 2

You can use the sequence designed for this:

 create table test (id serial); alter sequence test_id_seq cycle minvalue 0 maxvalue 3 start 0 restart; insert into test values(DEFAULT); insert into test values(DEFAULT); insert into test values(DEFAULT); insert into test values(DEFAULT); insert into test values(DEFAULT); insert into test values(DEFAULT); insert into test values(DEFAULT); insert into test values(DEFAULT); select * from test; id ---- 0 1 2 3 0 1 2 3 (8 rows)