CODEWITHSUNDEEP
postgresql
Werner Raath
Werner Raath

Reputation: 1512

PostgresQL for each row, generate new rows and merge

I have a table called example that looks as follows:

ID | MIN | MAX |
1  | 1   |  5  |
2  | 34  | 38  |

I need to take each ID and loop from it's min to max, incrementing by 2 and thus get the following WITHOUT using INSERT statements, thus in a SELECT:

ID | INDEX | VALUE
1  |  1    |   1
1  |  2    |   3
1  |  3    |   5
2  |  1    |   34
2  |  2    |   36
2  |  3    |   38

Any ideas of how to do this?

Upvotes: 0

Views: 720

Answers (2)

Bergi
Bergi

Reputation: 665130

The set-returning function generate_series does exactly that:

SELECT
  id,
  generate_series(1, (max-min)/2+1) AS index,
  generate_series(min, max, 2) AS value
FROM
  example;

(online demo)

The index can alternatively be generated with RANK() (example, see also @a_horse_­with_­no_­name's answer) if you don't want to rely on the parallel sets.

Upvotes: 3

user330315
user330315

Reputation:

Use generate_series() to generate the numbers and a window function to calculate the index:

select e.id, 
       row_number() over (partition by e.id order by g.value) as index,
       g.value
from example e
  cross join generate_series(e.min, e.max, 2) as g(value);

Upvotes: 2

Related Questions