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
