Sample Header Ad - 728x90

Cardinality of a multirange? How many ranges or gaps are there in a multirange?

1 vote
1 answer
107 views
How do I obtain the cardinality of a multirange? I'm using range_agg() to aggregate ranges, which merges ranges if they are continuous, or returns what looks like an array of ranges if the ranges are discontinuous. I want to detect if there are any gaps in the multirange and wanted to use cardinality() for this. A cardinality of 1 would mean no gaps, a cardinality of 2 means 1 gap, etc. However cardinality is not defined on a multirange, because a multirange is not technically an array: with x(a) as ( values (daterange(date '2024-01-30', date '2024-03-31', '[)')), (daterange(date '2024-04-01', date '2024-04-30', '[)')) ) select cardinality(range_agg(a)) from x; returns ERROR: function cardinality(datemultirange) does not exist LINE 6: select cardinality(range_agg(a)) from x; I can probably implement the function myself with unnest() and counting the number of resulting rows, but I'm curious if there are any other good solutions.
Asked by Colin 't Hart (9455 rep)
Sep 26, 2024, 07:42 AM
Last activity: Sep 26, 2024, 11:28 AM