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
