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?
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 &amp;&amp; $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 @&gt; $2 and not isempty($2) and lower($1) &lt;&gt; lower($2) and upper($2) &lt;&gt; 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.