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

Question

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...

Or you can use a recursive CTE. But generate_series is simpler. — The Impaler
– The Impaler, Commented Oct 1, 2021 at 16:10

Anton Grig · Accepted Answer · 2021-10-01 16:10:19Z

2

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

answered Oct 1, 2021 at 16:10

Anton Grig

1,71910 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related