PostgreSQL range_intersect_agg() Function

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

range_intersect_agg() Syntax

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

range_intersect_agg(expr) -> RANGE

Typically, we use the range_intersect_agg() function like:

SELECT range_intersect_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_intersect_agg() function returns an range that is the intersection of all non-NULL input range values ​​in a group.

This function will return empty if there is no intersection between multiple range values.

range_intersect_agg() Examples

To demonstrate usages of the PostgreSQL range_intersect_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 get the intersection ​​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_intersect_agg() function calculate the intersection of all range_value in each group. The following statement fulfills this requirement:

SELECT
    t.name,
    range_intersect_agg(t.range_value) range_intersect_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_intersect_agg
------+---------------------
 Tim  | empty
 Tom  | [4,7)
(2 rows)

here:

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