Constraint to prevent overlapping ranges in an array field in PostgreSQL

Question

If I have a tztrange field in a table, I can make sure that no two rows have values that overlap using an exclusion constraint. To make sure that no two appointments can be scheduled at the same time, for example, I can use:

CREATE TABLE appointment (
    schedule tstzrange,
    EXCLUDE USING gist (schedule WITH &&)
);

Is there a way to get this same behaviour, but for the values in an array field of type tstzrange[] in a single row?

---

As an explicit example, I want to be able to define a table test with a constraint that lets these successive inserts both succeed, because in each single array the two ranges don't overlap:

INSERT INTO test (distinct_ranges)
    VALUES ('{"[2016-01-01, 2016-02-01)", "[2016-03-01, 2016-04-01)"}');
INSERT INTO test (distinct_ranges)
    VALUES ('{"[2016-01-15, 2016-02-07)", "[2016-02-29, 2016-03-15)"}');

but such that this insert fails regardless of what other rows exist, because in this array the second range overlaps the first:

INSERT INTO test (distinct_ranges)
    VALUES ('{"[2016-01-01, 2016-02-01)", "[2016-01-15, 2016-02-07)"}');

---

What I've done so far:

First I thought I'd just look up the function that returned whether or not all ranges in an array were pairwise non-overlapping, and then use a check constraint. Unfortunately, that function doesn't seem to be built-in. I also considered using unnest and the * intersection operator, which would probably mean defining a custom aggregate function, although that's not quite right either unless I can get tricky with the aggregator, because the intersection of three ranges might be empty even though two of the ranges overlap.

So now I'm looking at unnesting the array, sorting it by the start of each range, and returning the logical OR of range1 && range2, range2 && range3, and so on up to range(n-1) && range(n), and checking that that result is false. It's far from obvious to me though what the most efficient way to do this is, and I'm still hoping that I've missed some built-in functionality that will help me out at least partially.

Answer 1

I had a similar problem with an array of int4ranges, and solved it (so far to my satisfaction) by writing this for WiTopia who has kindly agreed to let me share. Please satisfy yourself it works before using it in production -- I am still doing that!

CREATE OR REPLACE  FUNCTION overlapping(therange int4range[])
RETURNS BOOLEAN as
$$
BEGIN

        RETURN (
                with  
                      t1 as (select unnest(therange) as lefthand),
                      t2 as (select unnest(therange) as righthand)
                select (select array_length(therange, 1)) !=
                       (select count(*) from t1, t2 where t1.lefthand && t2.righthand)

       );

END;
$$ LANGUAGE plpgsql

I do a && (overlap) based join with the array and itself. Each item will join with itself, and should join with any other item it overlaps as well, so the count of the result of the &&-join would be greater than the count of the original array if there are overlapping items. If there are only non-overlapping items, then each item will join with itself only, and the result of the join will have one row for each element in the original array.

I'd be careful about using this once the arrays got big -- it's N^2 in concept!

After defining this function, I put this constraint in:

check (portsegments is null or not overlapping(portsegments))