0

Initial input:

CREATE TABLE TEST_TABLE(
   start_i INT,
   end_i INT,
   v REAL
);

INSERT INTO TEST_TABLE (start_i, end_i, v) 
VALUES (300,305,0.5),
(313,316,0.25)
start_i end_i v
300 305 0.5
313 316 0.25

Desired outcome:

Basically, I want to create intermediate rows with an additional column containing each value in the ranges shown in the initial table.

i start_i end_i v
300 300 305 0.5
301 300 305 0.5
302 300 305 0.5
303 300 305 0.5
304 300 305 0.5
305 300 305 0.5
313 313 316 0.25
314 313 316 0.25
315 313 316 0.25
316 313 316 0.25

I have checked this post, but it's for SQL Server, while I am interested in Postgres. In addition, I am not using a date column type, but an integer instead.

sqlquestionasker
  • 143
  • 2
  • 9

1 Answers1

1

Use generate_series():

select gs.i, t.*
from t cross join lateral
     generate_series(start_i, end_i, 1) gs(i);

Strictly speak, the lateral is not needed. But it does explain what is going on. I should also note that you can also do:

select generate_series(start_i, end_i) as i, t.*
from t;

However, generate_series() affects the number of rows in the query. I am uncomfortable with having such effects in the SELECT clause.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • thanks for the quick reply! Regarding your last comment, do you mean there is a performance downside to using `generate_series`? – sqlquestionasker May 05 '21 at 16:17
  • @sqlquestionasker . . . No. It is just weird to have a function in the `select` that affects the number of rows in the result set. – Gordon Linoff May 05 '21 at 17:54