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

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?

CodePudding user response：

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)

CodePudding user response：

Just as an alternative for other situations which are less appropriate to use generate_series, you could use a recursive CTE:

with recursive cte as (
  select 1 as price, 1 as different_table_foreign_key, 1 as rn
  union all
  select 1, 1, rn   1
  from cte
  where rn < 100
)
insert into test (price, different_table_foreign_key)
select price, different_table_foreign_key
from cte