sql - How to select overlapping value pairs from DB -

in postgres db have table 3 columns this:

start | end  | sorce 17    | 23   | 150   | 188  | 200   | 260  | 19    | 30   | b 105   | 149  | b 199   | 220  | b ... 

i select row regions (start end) differed sources , b overlapping.

update:

from postgres version 8.4 posible solve problem window functions. more faster join or subselect methodes. link postgres wiki.

this works brute-force approach (i renamed columns range_start , range_end avoid conflict reserved word "end"):

select * t cross join t t2 t2.source <> t.source       , box(point(t2.range_start,t2.range_start),point(t2.range_end,t2.range_end))             && box(point(t.range_start,t.range_start),point(t.range_end,t.range_end)) 

or

select * t exists (select 1 t t2               t2.source <> t.source , box(point(t2.range_start,t2.range_start),point(t2.range_end,t2.range_end))                   && box(point(t.range_start,t.range_start),point(t.range_end,t.range_end))) 

you should able use gist index may make more efficient (seq scan + index scan):

create index t_range_idx on t using gist (box(point(range_start,range_start),point(range_end,range_end)) 

this function might aid understanding clearing sql:

create function range(not_before int, not_after int) returns box    strict immutable language sql    $$ select box(point($1,$1),point($2,$2)) $$; 

with this, can write:

select * t range(range_start,range_end) && range(10,20); 

and note box && box operator means "overlaps".