PostgreSQL: result of range difference would not be contiguous

How to substract a smaller subrange from a bigger range?

``````123456789
567
``````

Result:

``````1234   89
``````

SQL:

``````select '[1,9]'::int4range - '[5,7]'::int4range;
ERROR:  result of range difference would not be contiguous
``````

The result does not fit into a int4range since it is not contiguous.

How to get the result as a set of int4ranges?

3 Answers

You could define your own functions range_add/range_sub (intersection doesn't need special handling):

``````select range_add('[1,5)'::int4range, '[8,10)'::int4range);
range_add
--------------------
{"[1,5)","[8,10)"}

select range_sub('[1,10)'::int4range, '[5,8)'::int4range);
range_sub
--------------------
{"[1,5)","[8,10)"}
``````

You could define them like this:

``````create or replace function range_add(int4range, int4range) returns int4range[] as
\$\$
select
case
when \$1 && \$2
then array[\$1 + \$2]
else array[\$1, \$2]
end
\$\$
language SQL;

create or replace function range_sub(int4range, int4range) returns int4range[] as
\$\$
select
case
when \$1 @> \$2 and not isempty(\$2) and lower(\$1) <> lower(\$2) and upper(\$2) <> upper(\$1)
then array[int4range(lower(\$1), lower(\$2), '[)'), int4range(upper(\$2), upper(\$1), '[)')]
else array[\$1 - \$2]
end
\$\$
language SQL;
``````

Unfortunately, PostgreSQL is missing native support for this set-based behavior.

A solution could be something like

``````CREATE TABLE test_ranges (
big_range int4range,
small_range int4range,
test_case text
);

INSERT INTO test_ranges
VALUES
('[1,9]'::int4range, '[5,7]'::int4range, 'smaller range splits bigger into two'),
('[1,9]'::int4range, '[1,9]'::int4range, 'smaller is the same as bigger'),
('[1,9]'::int4range, '[0,9]'::int4range, 'smaller is bigger'),
('[1,9]'::int4range, '[1,4]'::int4range, 'smaller removes the left part of bigger (same effect expected on the right side)'),
('[1,9]'::int4range, 'empty'::int4range, 'smaller is empty'),
('empty'::int4range, '[5,7]'::int4range, 'bigger is empty');

/* please note that you have to reorganize the query a bit
in order to get the 'halves' as a set */

SELECT
CASE WHEN isempty(small_range)
THEN big_range
ELSE big_range - int4range(lower(small_range), upper(big_range))
END AS first_half,
big_range - int4range(lower(big_range), upper(small_range)) AS second_half,
test_case
FROM test_ranges;
``````

Note that

``````test=# SELECT '[1,5)'::int4range = '[1,4]'::int4range;
?column?
──────────
t
``````

There is a small SQLFiddle to show this at work. Thanks to ypercube to bring my attention to one corner case that the original query did not cover.

Sven's answer above is very helpful, but I just want to add one edge case to make it more complete:

In `range_add`, the `when` expression should add a check for adjacent but not intersecting ranges with the `-|-` operator. So the corrected code for that line would be:

``````when \$1 && \$2 or \$1 -|- \$2
``````