3

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])
  • 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]. – joanolo Jul 8 '17 at 22:40
  • 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}. – Alexander Jul 8 '17 at 22:52
  • So you actually are checking for not null intersection. – joanolo Jul 8 '17 at 22:57
  • You'll probably need a functional index for int4range(EXTRACT(DECADE from start_date)::int, EXTRACT(DECADE from end_date)::int, '[]') – joanolo Jul 9 '17 at 11:55
3

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

1

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.

  • 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? – Alexander Jul 9 '17 at 2:21
  • No, you have to dynamically craft the query to use this method. – Evan Carroll Jul 9 '17 at 3:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.