PostgreSQL: Insert into new table N times based on column value from origin table-CodePudding

I have been tasked with remodelling a PostgreSQL database, and I am a bit stumped.

I have a table that looks similar to this:

| id | item_name | quantity | user_id |
| -- | --------- | -------- | ------- |
| 1  |   item1   |    1     |    1    | 
| 2  |   item2   |    3     |    6    |
| 4  |   item1   |    4     |    3    |
| 3  |   item3   |    5     |    6    |

What I need to do is insert these into another table as unique rows based on quantity.

Needed table structure based on above values:

| id | item_name | user_id |
| -- | --------- | ------- |
| 1  |   item1   |    1    |
| 2  |   item2   |    6    |
| 3  |   item2   |    6    |
| 4  |   item2   |    6    |
| 5  |   item1   |    3    |
| 6  |   item1   |    3    |
| 7  |   item1   |    3    |
| 8  |   item1   |    3    |
| 9  |   item3   |    6    |
| 10 |   item3   |    6    |
| 11 |   item3   |    6    |
| 12 |   item3   |    6    |
| 13 |   item3   |    6    |

I could do this pretty easily with a python script, but it is required for it to be done with Postgres and I lack a bit of the experience required in this specific scenario.

CodePudding user response：

Use generate_series() function for retrieving values multiple times with specific column values.

Example: generate_series(start_value, end_value, interval)

-- PostgreSQL
select t.id, t.name, t.user_id
from (select *, generate_series(1, quantity, 1) no_of_times
      from test) t

Please check this url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=351d8b5828ecd1050b78ac516329816b