0

First of all, im coming from this question, where I was recommended to use generate_series() , but after some adjustments in my db, I dont really understand how to create such sentence This is the old question: 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?

But I will rephrase the question again:

Those are my initial tables

  • Users: id(int) PK, weeks_to_live(int)
  • Calendar: id(int) PK, user_id (int) (FK -> Users(id))*
  • CalendarField: id(int) PK, text(text), rating(int), calendar_id(int) (FK ->Calendar(id))*, week_number(int)

Point is, when I generate my calendar in my app, actually my Users, and Calendar tables get filled properly. Upon a generation they will end up looking like this

users

id   weeks_to_live
1    3
2    2

calendar

id   user_id
1    1
2    2

That part is fine, but I need my CalendarField table to be autogenerated too, and thats where supposedly generate_series() should come into action, so they will end up looking like this

CalendarField

id    text     rating    calendar_id   week_number
1     ''       0         1             0
2     ''       0         1             1
3     ''       0         1             2
4     ''       0         2             0
5     ''       0         2             1

As you can observe:

  • id: is autoincremental
  • text: always defaults to "",
  • rating: always defaults to 0,
  • calendar_id (fk) : is the relation with each calendar, who has an amount of calendarFields row equaling to the number up to weeks_to_live in users
  • week_number: this number, should begin at 0, and keep incrementing while its < than weeks_to_live in the users table

Now my question is, how do I generate that last insert so my CalendarField table ends up filling with that info?. I suppose that with generate series, I wont need to create an enormous insert with == weeks_to_live values in case it gets huge. But I don't understand how to do it.

Or should I do a giant Insert then?

3

You need to join users and calendar and then cross join that with a generate_series().

insert into calendar_field (text, rating, calendar_id, week_number)
select '', 0, c.id, g.wn
from calendar c
  join users u on u.id = c.user_id
  cross join generate_series(0, u.weeks_to_live) as g(wn);

Online example

| improve this answer | |
  • It works, and thanks for your comment relating to the lowercase stuff since it was failing too because of that. I will change the names – mouchin777 Apr 10 at 11:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.