How to insert values into a postgres table using for loop?

Question

I have a script like this in postgres

begin;

INSERT INTO "schema"."table"(price, different_table_foreign_key)
VALUES
    (1, 1)

end;

for testing purposes I want to fill table 100 times with the same values as seen above.

how can I do this using a for loop?

score 1 · Accepted Answer · 2022-09-14 08:47:53Z

1

No need for a loop, you can use generate_series() for that:

INSERT INTO "schema"."table"(price, different_table_foreign_key)
select 1,1
from generate_series(1,100);

If you want a different value for each row, just use the one returned by `generate_series()

INSERT INTO "schema"."table"(price, different_table_foreign_key)
select 1, g.value
from generate_series(1,100) as g(value)

answered Sep 14, 2022 at 8:27

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Thank you it worked. But I made a mistake in my question. If I wanted different_table_foreign_key to go from 1 to 100, how would i do that?

@PestyLeader: see my edit