Question 280176 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL – numbers between two integer columns

I’m trying to figure out a way to list all numbers between two columns (INT) and join them in a third column where all numbers are seperated.

Eg.

Column A Column B Column C
100 105 100;101;102;103;104;105
10 14 10;11;12;13;14

Does anyone have a solotion for this?

Have googled around but didnt find any exact match

Tags:

2

Answers


  1. 0

    Use the generate_series which is a function to generate a series of numbers between two values. with the help of string_agg function to concatenate these numbers into a single string, separated by semicolons:

    SELECT
  column_a,
  column_b,
  string_agg(generated_number :: text, ';') AS column_c
FROM
  (
    SELECT
      column_a,
      column_b,
      generate_series(column_a, column_b) AS generated_number
    FROM
      your_table
  ) AS subquery
GROUP BY
  column_a,
  column_b;

    dbfiddle DEMO

    Login or Signup to reply.
  2. 0

    Set-returning generate_series() does just that: demo

    create table test(start_ int, end_ int);
insert into test values (100,105),(10,14);

select start_, end_, string_agg(numbers_between::text,';') 
from test, generate_series(start_,end_)_(numbers_between)
group by start_, end_;
    start_ end_ string_agg
    10 14 10;11;12;13;14
    100 105 100;101;102;103;104;105

    String_agg() does what you want but array_agg() or jsonb_agg() are preferable because they maintain the adequate type and allow you to subscript the resulting array.

    In AWS Redshift flavour of PostgreSQL, generate_series() isn’t supported, which is why you’d have to use a recursive cte mentioned by @jarlh:

    with recursive cte as(
  select start_,end_,start_ as number_between from test
  union all select start_,end_,number_between+1 from cte
  where number_between+1<=end_)
select start_,end_,string_agg(number_between::text,';')
from cte
group by start_,end_;

    Which works in regular PostgreSQL as well.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search