Reputation: 4151
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
Upvotes: 0
Views: 1658
Reputation:
You can use generate_series() for that:
select t.*
from the_table t
cross join generate_series(t, d.num2 - t.num1)
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)
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)
Upvotes: 3
Reputation: 147286
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
Upvotes: 2