How to search using daterange (using gist)

Am 16.05.19 um 11:57 schrieb Winanjaya Amijoyo:

Hi All,

I have records as below that I inserted using exclusion gist constraint

user_id    start_date      end_date         pid
001          2019-01-01    2019-02-10        1
001          2019-02-01    2019-03-12        2
001          2019-03-05    2019-06-15        3

I need to find records based on date range, so for example:

if I search using parameters 001, 2019-01-10, 2019-02-11 .. it will
found as pid 1
if I search using parameters 001, 2019-03-10, 2019-07-13 .. it will
found as pid 3
but if I search out of the date range, for example:
if I search using parameters 001, 2019-07-10, 2019-09-13 .. it will
not found as pid 0

how to search with the above scenario in postgresql?

can you please show us the table-definition? Are you sure there is an
exclusion constraint?

with your data:

test=*# select * from demo;
 user_id | start_date |  end_date  | pid
---------+------------+------------+-----
       1 | 2019-01-01 | 2019-02-10 |   1
       1 | 2019-02-01 | 2019-03-12 |   2
       1 | 2019-03-05 | 2019-06-15 |   3
(3 rows)

test=*# select * from demo where daterange(start_date, end_date,'[)') &&
daterange('2019-01-10','2019-02-11');
 user_id | start_date |  end_date  | pid
---------+------------+------------+-----
       1 | 2019-01-01 | 2019-02-10 |   1
       1 | 2019-02-01 | 2019-03-12 |   2
(2 rows)

test=*# select * from demo where daterange(start_date, end_date,'[)') &&
daterange('2019-03-10','2019-07-13');
 user_id | start_date |  end_date  | pid
---------+------------+------------+-----
       1 | 2019-02-01 | 2019-03-12 |   2
       1 | 2019-03-05 | 2019-06-15 |   3
(2 rows)

test=*# select * from demo where daterange(start_date, end_date,'[)') &&
daterange('2019-07-10','2019-09-13');
 user_id | start_date | end_date | pid
---------+------------+----------+-----
(0 rows)

test=*#

test=*# \d demo
                  Table "public.demo"
   Column   |  Type   | Collation | Nullable | Default
------------+---------+-----------+----------+---------
 user_id    | integer |           |          |
 start_date | date    |           |          |
 end_date   | date    |           |          |
 pid        | integer |           |          |

test=*#

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com