How to check if integer array overlaps with int4range?

Question

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 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])

• joanolo: Your question is not clear. Let's consider this as set operations. Overlaps (as in your title) means {1,2,3,4,5,6,7,8} has a not null intersection with range[4, 5]. On the other hand, I don't know what it exactly means an array exists in the range (as in your text). Does this mean, it is contained in. That would mean {1,2, .. 8} is not contained in range[4, 5].
• Alexander: Thanks for your comment. In your example, I would like to check if either 4 or 5 is in the range, but I'd like to do this with an array. So if my array is [1, 3, 4], I'd like to check if either 1, 3, or 4 is contained in the range of {1, 2, 3, 4, 5, 6, 7, 8}.
• joanolo: So you actually are checking for not null intersection.
• joanolo: You'll probably need a functional index for int4range(EXTRACT(DECADE from start_date)::int, EXTRACT(DECADE from end_date)::int, '[]')

Answer 1

You can use the 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

Answer 2

You may have better performance unrolling it and dropping the range operation.

WHERE x1 BETWEEN start_date AND end_date
 OR x2 BETWEEN start_date AND end_date

The range operation is fast, but only if you have a separate indexes on

int4range(EXTRACT(DECADE from start_date)::int, EXTRACT(DECADE from end_date)::int;

Whereas the WHERE BETWEEN can make use of the indexes available. It's arguably a bad idea to add indexes that pick decade arbitrarily.

• Alexander: Thanks for your answer and insight on performance! I don't have any indexes on the start_date or end_date columns. I extract the decade because that is the only part of the date that is important in this query. My example was simple in nature; I actually need this query to work for any number of decades specified in the array. Would your approach handle that case without requiring a dynamically crafted query?
• Evan Carroll: No, you have to dynamically craft the query to use this method.