1

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

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.