snippetsqlMinor
How to check if integer array overlaps with int4range?
Viewed 0 times
arraywithoverlapsint4rangehowcheckinteger
Problem
PostgreSQL version: 9.5
I am trying to write a query that will check if an array of specified decades overlaps in a range. For example, I'd like to find all of the people employed at a company in the 1980's OR the 2000's. Let's say I have two
This query will check for anyone employed in the 1980s, 1990s, or the 2000s. I don't want to compare a range to another range; instead, it should be checking if any of the values in the array is contained in the range. How can I accomplish this? Is there another way I could be approaching this problem?
UPDATE
Thanks to the answer by joanolo, my working query is:
I am trying to write a query that will check if an array of specified decades overlaps in a range. For example, I'd like to find all of the people employed at a company in the 1980's OR the 2000's. Let's say I have two
date columns: start_date and end_date. Here's my best attempt so far:SELECT *
FROM date_table
WHERE int4range(EXTRACT(DECADE from start_date)::int,
EXTRACT(DECADE from end_date)::int) && (int4range(198, 200))This query will check for anyone employed in the 1980s, 1990s, or the 2000s. I don't want to compare a range to another range; instead, it should be checking if any of the values in the array is contained in the range. How can I accomplish this? Is there another way I could be approaching this problem?
UPDATE
Thanks to the answer by joanolo, my working query is:
SELECT *
FROM date_table
WHERE int4range(EXTRACT(DECADE from start_date)::int,
EXTRACT(DECADE from end_date)::int, '[]') @> ANY(ARRAY[198, 200])Solution
You can use the
If you want to know if ANY element of the array lies within your range, which is what, according to your comments, is what I interpret you want, you can use:
(in this case, this will return TRUE, because both 5 and 7 are part of the range)
This nearly translates your request (but reversing it): Does the range contain ANY of the elements of the array?
If you would like to know that they're all in, you'd use
(in this case, this will return false, because 1 and 3 are not part of the range)
You can find these and some other examples at dbfiddle here
ANY and ALL array element quantifiers, together with the @> contains element range operator.If you want to know if ANY element of the array lies within your range, which is what, according to your comments, is what I interpret you want, you can use:
SELECT
int4range(5, 8, '[]') @> ANY(ARRAY[1, 3, 5, 7]) AS some_are_in(in this case, this will return TRUE, because both 5 and 7 are part of the range)
This nearly translates your request (but reversing it): Does the range contain ANY of the elements of the array?
If you would like to know that they're all in, you'd use
ALL instead of ANY:SELECT
int4range(5, 8, '[]') @> ALL(ARRAY[1, 3, 5, 7]) AS all_are_in(in this case, this will return false, because 1 and 3 are not part of the range)
You can find these and some other examples at dbfiddle here
Code Snippets
SELECT
int4range(5, 8, '[]') @> ANY(ARRAY[1, 3, 5, 7]) AS some_are_inSELECT
int4range(5, 8, '[]') @> ALL(ARRAY[1, 3, 5, 7]) AS all_are_inContext
StackExchange Database Administrators Q#179376, answer score: 3
Revisions (0)
No revisions yet.