Postgresql – Populate a table that should have an X amount of rows with default values with a big insert query? Or can it done somehow by default

database-designinsertpostgresqlquery

I have a table lets say calendar

Table Calendar --> idCal (int) , numberOfFields (int)

And each Calendar has asigned a number of fields

Table Field --> idField(int), textField(text), idCal (int) *fk

Point is right now, each time an user register, he is assigned a calendar, once the numberOfFields is populated, I select that value and generate an insert query similar to:

INSERT INTO Field (textField, idCal) Values ("","idOfTheGeneratedCalendar") , ("","idOfTheGeneratedCalendar") ......

Until I have a number of rows equivalent to numberOfFields from the table Calendar. Where each idField(int) begins at 0, and its an autoincrement up to whatever the numberOfFields is

I do that for each user. Point is… is there a better way to do this without building huge insert queries with aprox 3000 values each using a for iteration? Should I be concerned?

Best Answer

you can use generate_series() for that:

insert into field (textfield, idcal)
select '', c.idcal
from calendar c
where idcal = 42
  cross join generate_series(1, c.numberoffields) as g(f)
Related Question