PostgreSQL : How to duplicate row for certain number?

Question

I have a sample table like this

col1    col2    col3    num1    num2
------------------------------------
val1    val2    val3     1       5
val4    val5    val6     7       15
val7    val8    val9     18      26

I want the row become as many as the difference between num2 and num1. For example, I want the first row to be 4 rows (5 - 1 = 4).

Expected result:

col1    col2    col3    num1    num2
------------------------------------
val1    val2    val3     1       5
val1    val2    val3     1       5
val1    val2    val3     1       5
val1    val2    val3     1       5
val4    val5    val6     7       15
val4    val5    val6     7       15
val4    val5    val6     7       15
val4    val5    val6     7       15
val4    val5    val6     7       15
val4    val5    val6     7       15
val4    val5    val6     7       15
val4    val5    val6     7       15
val7    val8    val9     18      22
val7    val8    val9     18      22
val7    val8    val9     18      22
val7    val8    val9     18      22

Referring to the answer here postgreSQL: how to duplicate a row, is it possible to duplicate without primary key? or PK is absolutely needed? Then what is the most effective way to achieve this? I'm thinking about using for loop as general programming, but I believe there's a simpler way using SQL

Answer 1

You can use generate_series() for that:

select t.*
from the_table t
  cross join generate_series(t, d.num2 - t.num1)

Online example

If you want to insert those rows into the table, put an INSERT in front of it:

insert into the_table(col1, col2, col3, num1, num2)
select t.*
from the_table t
  cross join generate_series(1, t.num2 - t.num1)

Online example

To include negative difference (num2 < num1) and remove unnecessary original row

insert into the_table(col1, col2, col3, num1, num2)
select t.*
from the_table t
  cross join generate_series(1, abs(t.num2 - t.num1) - 1) 

Answer 2

You can use a recursive CTE to generate the duplicate rows and then an INSERT...SELECT query to insert them:

WITH RECURSIVE CTE AS (
  SELECT col1, col2, col3, num1, num2, num1 + 1 AS cnt
  FROM data
  UNION ALL
  SELECT col1, col2, col3, num1, num2, cnt + 1
  FROM CTE
  WHERE cnt < num2 - 1
)
INSERT INTO data
SELECT col1, col2, col3, num1, num2
FROM CTE
;
SELECT *
FROM data
ORDER BY col1

Output

col1    col2    col3    num1    num2
val1    val2    val3    1       5
val1    val2    val3    1       5
val1    val2    val3    1       5
val1    val2    val3    1       5
val4    val5    val6    7       15
val4    val5    val6    7       15
val4    val5    val6    7       15
val4    val5    val6    7       15
val4    val5    val6    7       15
val4    val5    val6    7       15
val4    val5    val6    7       15
val4    val5    val6    7       15
val7    val8    val9    18      22
val7    val8    val9    18      22
val7    val8    val9    18      22
val7    val8    val9    18      22

Demo on SQLFiddle