PostgreSQL range_agg() Function

The PostgreSQL range_agg() function is an aggregate function that returns the union of all non-NULL input range values ​​in a group.

range_agg() Syntax

Here is the syntax of the PostgreSQL range_agg() function:

range_agg(expr) -> MULTIRANGE

Typically, we use the range_agg() function like:

SELECT range_agg(expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

Parameters

expr

Required. A column name or expression.

Return value

The PostgreSQL range_agg() function returns an array containing the union of all non-NULL input range values ​​in a group.

range_agg() Examples

To demonstrate usages of the PostgreSQL range_agg() function, we simulate a temporary table using the following statement with UNION and SELECT:

SELECT 'Tim' name, '[3,7)'::int4range range_value
UNION
SELECT 'Tim' name, '[8,10]'::int4range range_value
UNION
SELECT 'Tom' name, '(3,7)'::int4range range_value
UNION
SELECT 'Tom' name, '[4,9)'::int4range range_value;

 name | range_value
------+-------------
 Tom  | [4,9)
 Tom  | [4,7)
 Tim  | [3,7)
 Tim  | [8,11)
(4 rows)

Here, we have some rows for the range value selected by the user. The name column is the user’s name, and range_value the column is a range value for a user.

Suppose, to union all the range values ​​for each user (users with the same name count as one), you can use the GROUP BY clause to group all rows by name and use the range_agg() function calculate the union of all range_value in each group. The following statement fulfills this requirement:

SELECT
    t.name,
    range_agg(t.range_value) range_agg
FROM (
    SELECT 'Tim' name, '[3,7)'::int4range range_value
    UNION
    SELECT 'Tim' name, '[8,10]'::int4range range_value
    UNION
    SELECT 'Tom' name, '(3,7)'::int4range range_value
    UNION
    SELECT 'Tom' name, '[4,9)'::int4range range_value
) t
GROUP BY t.name;

 name |   range_agg
------+----------------
 Tim  | {[3,7),[8,11)}
 Tom  | {[4,9)}
(2 rows)

here:

• For Tim, the union of [3,7) and [8,10] is {[3,7),[8,11)}.
• For Tom, the union of (3,7) and [4,9) is {[4,9)}.