4

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?

4

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.

3

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.