There aren't any Aggregate functions for Range types in PostgreSQL.

How to aggregate ranges using a merge operation?

This solution works but does not look optimal:

CREATE TEMPORARY TABLE test_ranges (r int4range);
INSERT INTO test_ranges VALUES ('[4, 8)'), ('[12, 45)'), (NULL);

SELECT int4range(min(lower(r)), max(upper(r))) FROM test_ranges;
=> '[4,45)'
  • What's your actual question? Are you just looking for the smallest possible range containing all the ranges in your selection? Your approach looks correct at first sight, but the answer will depend on the inclusiveness/exclusiveness of both bounds... which I don't think is taken into account if there were [1,2), (1,2), [1, 2], (1, 2] – joanolo May 8 '17 at 20:13
  • 1
    The solution given in the question does not handle +/- infinity correctly. Victor's solution does. – fjf2002 Jan 18 '18 at 15:40

Create an aggregate using the range_merge(anyrange, anyrange) function.

CREATE AGGREGATE range_merge(anyrange)
  sfunc = range_merge,
  stype = anyrange

SELECT range_merge(r) FROM test_ranges;
=> '[4,45)'
| improve this answer | |
  • How would you deal in your range_merge function the inclusiveness or exclusiveness of your bounds? – joanolo May 8 '17 at 20:14
  • 1
    @joanolo According to the documentation: The smallest range which includes both of the given ranges. I guess inclusiveness or exclusiveness are handled internally by PostgreSQL. Do you have an example that would be problematic? – Victor May 8 '17 at 20:31
  • You're right. My bad... I didn't read the documentation carefully enough. – joanolo May 8 '17 at 20:50
  • Isn't this slower than the simple solution in the question (for a non-trivial number of rows to aggregate)? – Erwin Brandstetter May 9 '17 at 1:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.