insert series into postgreSQL

Question

I want to insert a series of numbers in a postgreSQL column.The series will be

1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3.....30

So 1-30 repeated 10 times each(300 rows).but I know to generate series I can use

 SELECT a.n
from generate_series(1, 100) as a(n), generate_series(1, 3)

How to insert this series into an existing table public.trip and to the column day.I tried with

update public.trip set day = generate_series(1, 30) , generate_series(1, 10);

But this is showing as an error.Any help is appreciated.

http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 — e4c5, Dec 19 '16 at 07:50
I wanted this because I have to cook some dummy data.So I am not using this practice for real.Just to cook to some dummy data.Anyways thanks for sharing the knowledge — RKR, Dec 19 '16 at 07:55

score 6 · Accepted Answer · answered Dec 19 '16 at 08:00

If you are inserting it as new data then you should use an INSERT INTO statement i this case it will be (I am assuming a.n is where the value of day is contained)

INSERT INTO public.trip(Day)
   SELECT a.n from generate_series(1, 100) as a(n), generate_series(1, 3)

if you are updating an existing record then you can use the UPDATE statement

UPDATE public.trip SET Day =  SELECT a.n from generate_series(1, 100) as a(n), generate_series(1, 3) WHERE [condition]

I hope this help

insert series into postgreSQL

1 Answers1