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

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?

Answer

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;

Advertisement

Answer