CODEWITHSUNDEEP
sqlpostgresql
Ana Cruz
Ana Cruz

Reputation: 103

Postgres: inserting as many rows as a value on another table

I'm trying to implement a ticket service of sorts, where each user has a certain number of tickets assigned to them. I have one table, users, that stores the name of the user and the number of tickets that will be assigned to them. But I have another table, tickets, that should have an entry for each ticket available with a foreign key that references its owner from the table users.

So, for example, if my table users contains:

User_id Name Quantity
1 Ana 3
2 Mark 4

I would like to have in my tickets table:

ticket_id user_id
1 1
2 1
3 1
4 2
5 2
6 2
7 2

Where ticket_id is serial.

Is there a easy way to insert as many rows as the quantity indicates in users? I don't want to insert them all by hand...

Upvotes: 0

Views: 585

Answers (1)

Anton Grig
Anton Grig

Reputation: 1719

It could be done via Inner Join Lateral with generate_series.

Insert Into Tickets (user_id)
Select user_id
From Users Inner Join Lateral generate_series(1, Quantity) As t On true
Order by user_id

Upvotes: 2

Related Questions