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

Question

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?

score 13 · Accepted Answer · answered Oct 17 '11 at 13:47

13

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;

answered Oct 17 '11 at 13:47

Mark Byers

811,555
193
1,581
1,452

1

Or if the table already exists you can just do `INSERT INTO egg SELECT ... FROM ...` – Innokenty Jul 11 '16 at 12:53

score 4 · Answer 2 · answered Oct 18 '11 at 16:17

You could use a sequence designed to do just that:

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)

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

2 Answers2