PostgresQL for each row, generate new rows and merge

Question

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?

stackoverflow.com/q/23358333/1048572

Bergi
– Bergi

2020-05-06 12:47:17 +00:00
Commented May 6, 2020 at 12:47 — Bergi
– Bergi, Commented May 6, 2020 at 12:47

Bergi · Accepted Answer · 2020-05-06 13:01:38Z

3

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.

edited May 6, 2020 at 13:01

answered May 6, 2020 at 12:51

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

1 Comment

Werner Raath Over a year ago

The online demo was a nice touch!

user330315 · Accepted Answer · 2020-05-06 12:53:51Z

2

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);

answered May 6, 2020 at 12:53

user330315

Collectives™ on Stack Overflow

PostgresQL for each row, generate new rows and merge

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related