How to expand each row of a table into a variable number of rows in Postgresql

Question

I have a table that defines objects with a given number of iterations, let's say it's a game with a variable number of rounds.

# select * from game;

game_id | num_rounds
--------+-----------
A       | 2
B       | 3
C       | 1

I'd like to create a select statement that will generate a table with one row per round for every game.

# select ???;

game_id | round_number
--------+-------------
A       | 1
A       | 2
B       | 1
B       | 2
B       | 3
C       | 1

Answer 1

No need for a subquery and a join in generate_series(). A lateral join is simpler, and more efficient:

select g.game_id, s.round_number
from game g
cross join lateral generate_series(1, g.num_rounds) as s(round_number)
order by g.game_id, s.round_number

Answer 2

Using a join on a generate_series with the maximum length possible does the job.

select
  game_id,
  generate_series as round_number
from game
join generate_series(1, (select max(num_rounds) from game))
  on generate_series <= num_rounds