I am trying to work with PostgreSQL arrays. I have added a column:

dates date[]

which contains an array of dates. I would like to select rows which have at least one date inside a range.

The only solution I have found that works is:

SELECT *
FROM archive
WHERE '2021-07-01' <= ANY(dates) AND '2021-09-30' >= ANY(dates);

which, in my opinion, looks unnatural. I would normally have put the target value after the comparison operator.

I would have preferred something like:

SELECT *
FROM archive
WHERE ANY(dates) BETWEEN '2021-07-01' AND '2021-09-30';

but that doesn’t work.

Is there an alternative way to do this search?

CodePudding user response：

You could use this WHERE condition:

WHERE daterange('2021-07-01', '2021-09-30', '[]') @> ANY (dates)

This will test whether the interval contains any of the dates.

It might be helpful to explain some of the voodoo:

• daterange() without the third argument creates a half-open range — it includes the first, but not the last date. To create a closed date range, you need a third argument '[]'. See the documentation for details.

• the @> operator tests whether the element on the right hand side is contained in the interval on the left hand side. In this case, does the first range include the second? See the documentation for details.

• operator ANY (array) compares the left hand expression with each member of the right hand array. If any element of the array satisfies the comparison, the result is TRUE. Again, here is a documentation link.