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

Can you post your expected result as well?

Jim Macaulay
– Jim Macaulay

2020-06-10 05:02:24 +00:00
Commented Jun 10, 2020 at 5:02 — Jim Macaulay
– Jim Macaulay, Commented Jun 10, 2020 at 5:02
@JimMacaulay edited

iv67
– iv67

2020-06-10 05:07:31 +00:00
Commented Jun 10, 2020 at 5:07 — iv67
– iv67, Commented Jun 10, 2020 at 5:07

iv67 · Accepted Answer · 2020-09-16 23:57:51Z

3

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)

edited Sep 16, 2020 at 23:57

iv67

4,1815 gold badges40 silver badges68 bronze badges

answered Jun 10, 2020 at 5:22

user330315

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

3 Comments

user330315 Over a year ago

If you want to insert the rows, then put an insert in front of the select (see my edit)

iv67 Over a year ago

Thanks a lot, sorry I didnt include this in the sample, but what if my row has float number like this num1=261.8 num2=262.5. I expect the row will be 7 as the difference is 7. I changed the 1 to 0.1 but it gave me wrong result

iv67 Over a year ago

Nevermind, I got it right after reading the documentation. I changed the int to float or numeric, then change cross join line to cross join generate_series(1, (t.num2 - t.num1)*10)

Nick · Accepted Answer · 2020-06-10 05:08:21Z

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

answered Jun 10, 2020 at 5:08

Nick

147k23 gold badges67 silver badges106 bronze badges

2 Comments

iv67 Over a year ago

Thank you, if I change the SELECT col1, col2, col3, num1, num2 to SELECT * but it gives me error, do you know the reason behind that?

Nick Over a year ago

There is an extra column in the CTE (cnt) which doesn't belong in the table, so you can't use SELECT *

Collectives™ on Stack Overflow

PostgreSQL : How to duplicate row for certain number?

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related