PostgreSQL: result of range difference would not be contiguous

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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