5

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)'
Improve this question
2
  • 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, 2017 at 20:13
  • 1
    The solution given in the question does not handle +/- infinity correctly. Victor's solution does.
    – fjf2002
    Jan 18, 2018 at 15:40

1 Answer 1

Reset to default
9

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
4
  • How would you deal in your range_merge function the inclusiveness or exclusiveness of your bounds?
    – joanolo
    May 8, 2017 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, 2017 at 20:31
  • You're right. My bad... I didn't read the documentation carefully enough.
    – joanolo
    May 8, 2017 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, 2017 at 1:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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