# Multi-valued range subtraction

I have a table (actually a query, but I think it's the same thing) with two range columns. I need to perform a 'subtraction' on the two ranges. Precisely, I need `Intersection(a, Complement(b))`. Since this is not representable with a single range value, there could be 0, 1, or 2 rows in the output for each input row. Here's an example:

``````CREATE TABLE test (some_data VARCHAR, a INT4RANGE, b INT4RANGE);
INSERT INTO test VALUES ('a', '[0, 5)', '[1, 2)');
INSERT INTO test VALUES ('b', '[0, 5)', '[1, 5)');
INSERT INTO test VALUES ('c', '[0, 5)', '[0, 5)');
INSERT INTO test VALUES ('d', '[1, 3)', '[4, 5)');

SELECT * FROM test;
some_data |   a   |   b
-----------+-------+-------
a         | [0,5) | [1,2)
b         | [0,5) | [1,5)
c         | [0,5) | [0,5)
d         | [1,3) | [4,5)
``````

And this is the result I want:

``````SELECT somequery... FROM test;

some_data |  a-b  |
-----------+-------+
a         | [0,1) |
a         | [2,5) |
b         | [0,1) |
d         | [1,3) |
``````

Looks like simple math. Assuming that all the ranges are of the same inclusive-exclusive type `[)`:

``````SELECT some_data, int4range(lower(a), LEAST(upper(a),lower(b))) AS ab
FROM test
WHERE lower(a) < lower(b)

UNION ALL

SELECT some_data, int4range(GREATEST(lower(a),upper(b)), upper(a))
FROM test
WHERE upper(b) < upper(a)

UNION ALL

SELECT some_data, a
FROM test
WHERE b = int4range(0,0)
OR a = int4range(0,0) ;
``````

Tested at SQL-Fiddle.