PostgreSQL – Multi-Valued Range Subtraction

postgresql

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) |

Best Answer

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.

Related Question