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 && $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.

StackExchange Database Administrators Q#97779, answer score: 4