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