Getting the outer range of a grouped column of range types in Postgres

user_10815141 0
Viewed 27

I have a table with a column name consisting of text values that I want to group by. Additionally I have a table value consisting of numerical range values (of range type). I want to group by the name column and get the outer bounds for each of the groups.

Here is an example:

Parameter table

name                  value
-----                 -----
Nominal power         [1,2]
Nominal power         [2,3]
Nominal power ratio   [10,15]
Nominal power ratio   [17,19]
Hydrogen production   [1,6]
Hydrogen production   [2,9]

Wanted output is something like this (or lower, upper bound in separate columns):

name                  outer range
-----                 -----
Nominal power         [1,3]
Nominal power ratio   [10,19]
Hydrogen production   [1,9]

Here is a query I attempted to run, but it failed:

select name, upper(value), lower(value) from parameters
group by name

Is there a way to achieve this is Postgresql?

postgresql sql
2 Answers

Edit to add: I recommend that you accept the answer by @a_horse_with_no_name because it is simpler, gives the exact answer you want, and works with versions prior to PostgreSQL 14. I would delete my answer here except that I find the multirange data type interesting.

Using range_agg() and the range_merge() on the new multirange types can do this:

select name, 
       range_merge(range_agg(value)) as outer_range
  from parameters
 group by name
 order by outer_range;

db<>fiddle here

user_138083190

You need to use an aggregate function, e.g.: min(lower(value)). From there you can create a new range:

select name, int4range(min(lower(value)), max(upper(value)), '[]')
from parameters
group by name;
user_3303150
Related