How to insert data in postgresql using for loop?

Question

I would like to know how to insert data in postgresql using for loop?

I want to insert 1 to 1000 in row of id..

What is the actual problem you are trying to solve? – Dec 14 '16 at 06:46 — , Dec 14 '16 at 06:46

score 82 · Answer 1 · answered Dec 14 '16 at 05:39

82

Please try below code to insert values in tables using for loop.

do $$
begin
for r in 1..1000 loop
insert into schema_name.table_name(id) values(r);
end loop;
end;
$$;

answered Dec 14 '16 at 05:39

Riya Bansal

score 23 · Answer 2 · answered Dec 14 '16 at 02:02

23

INSERT INTO tableName (id) SELECT * FROM generate_series(1, 1000);

answered Dec 14 '16 at 02:02

Philip Tzou

15

is there a reason to avoid it? @Evan Carroll – krithikaGopalakrishnan Dec 15 '16 at 10:36
2

@EvanCarroll In case you want to generate some test data, or want to performance benchmark, you can always use this. – Sameer Oct 13 '20 at 13:23
Yes, it's good for test data. Reasons to avoid it -- if it's a real ID column, it should be an identity column. If it's numbers on output you should use `ROW_NUMBER() OVER ()`, etc. – Evan Carroll Oct 13 '20 at 14:50
this one's faster than the loop – MarianP Sep 23 '22 at 11:02

2 Answers2