How to query complex range in postgreSql

Question

I have two columns in my one postgreSQL Table.

The name of columns are: TO, FROM which denotes a range. Let say 100 - 200 (so here 100 will save in TO and 200 will get saved in FROM)

Now, the main problem is any number in this range will be never be used again. i.e we can't save (100,101,102...200 according to the example mentioned above).

I have to create a query in which I have to take data from the request and check whether the requested range is in the database or not.

Example: 100 -200 are already saved in database and I am getting request of following: 1) 100 -200 2) 150-250 3) 20 -150 4) 50 -280 e.t.c... All the above cases are false because at least one number is already saved in db

I am new to SQL can you please help in querying? I am using postgreSQL.

Isn't it that FROM is 100 and 200 is TO, not the other way round? — jira
– jira, Commented Nov 12, 2017 at 12:15
But other ranges. Let say 150- 250 also contains 50 numbers (From 150 to 200 ) which are already put into the database. Am I right? — Anand Vaidya
– Anand Vaidya, Commented Nov 12, 2017 at 12:49
You don't actually need a query to check for that. In Postgres you can create an exclusion constraint that will automatically prevent the insert of overlapping ranges — user330315
– user330315, Commented Nov 12, 2017 at 13:04
Testing for overlapping ranges in any language requires just two conditions if your ranges are in the right order (start <= end): end2 >= start1 && end1>= start2 — jcaron
– jcaron, Commented Nov 12, 2017 at 13:05
Yes I have also made a query (FROM <= Req.from AND TO >= Req.from) OR (FROM <= Req.to AND To >= Req.to) OR (FROM >= Req.from AND To <= Req.to). But is there any other better way to do it ? — Anand Vaidya
– Anand Vaidya, Commented Nov 12, 2017 at 13:18

Anand Vaidya · Accepted Answer · 2017-11-14 09:05:58Z

1

SELECT * FROM TABLE_NAME WHERE 
(dateFrom <= Req.from AND dateTo >= Req.from)OR
(dateFrom <= Req.to AND dateTo >= Req.to) OR
(dateFrom >= Req.from AND dateTo <= Req.to)

I didn't find better then this

answered Nov 14, 2017 at 9:05

Anand Vaidya

6493 gold badges21 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jira · Accepted Answer · 2017-11-12 12:14:00Z

0

Postgres has range functions. Did yo look at it? So you can use the overlap operator for example to check whether ranges, well, overlap.

int8range(3,7) && int8range(4,12)

answered Nov 12, 2017 at 12:14

jira

3,9643 gold badges25 silver badges32 bronze badges

1 Comment

Anand Vaidya Over a year ago

Sure Let me check it.

Collectives™ on Stack Overflow

How to query complex range in postgreSql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related