Matching overlapping daterange values in PostgreSQL 9.3

Question

Is it possible to match overlapping datarange (datatype: daterange) values?

E.g. two tables containing daterange columns, one daterange column (table1.c1) has daterange values which encompass a 1-year period and the other daterange column (table2.c2) has daterange values which encompass 20-year periods.

How can I match the rows from table1 where the daterange of 'c1' overlaps with the daterange of 'c2' in table2?

I have tried using the OVERLAPS function but that led to a syntax error.

I'm using PostgreSQL 9.3.

Answer 1

There is no OVERLAPS operator in PostgreSQL.

regress=> SELECT daterange(DATE '2014-04-01', DATE '2014-04-28') OVERLAPS daterange(DATE '2014-04-14', DATE '2018-01-01');
ERROR:  syntax error at or near "OVERLAPS"
LINE 1: ...T daterange(DATE '2014-04-01', DATE '2014-04-28') OVERLAPS d...

I have no idea where you got that from. Are you really using PostgreSQL, or some third party fork? Are you reading the PostgreSQL documentation, or some tutorial/docs for some other database product?

I think you want what the PostgreSQL user manual recommends, the && operator:

regress=> SELECT daterange(DATE '2014-04-01', DATE '2014-04-28') && daterange(DATE '2014-04-14', DATE '2018-01-01');
 ?column? 
----------
 t
(1 row)