How to use BETWEEN condition for array column in Postgres?

I have a multiple_dates column in a Postgres DB.
To find by a specific date I’m writing like this:

SELECT * FROM companies
WHERE  '2019-06-30' = ANY (multiple_dates)

But I want to search by a specific period by using BETWEEN clause like this:

SELECT * FROM companies
WHERE  (ANY (multiple_dates) BETWEEN '2019-06-01' AND '2019-06-30')  -- pseudo-code

This SQL didn’t work. How can I use ANY and BETWEEN clause at the same time?

Answer

The “problem” is that the ANY construct works for operators, not for other constructs – and BETWEEN is another construct.

Related:

• Can IS DISTINCT FROM be combined with ANY or ALL somehow?

There is an easy solution for this, though. Construct a daterange from the given bounds and use the contains operator @>.

Related:

• How to use the NOW() function as upper bound of a range?

Then the query can simply be:

SELECT *
FROM   companies
WHERE  daterange('2019-06-01', '2019-06-30', '[]') @> ANY (multiple_dates)

Note the third argument '[]' to construct the range with inclusive bounds to match the behavior of BETWEEN.

---

Alternative: normalize your schema. Create a n:1 table like:

CREATE TABLE company_date
   company_id  int NOT NULL REFERENCES companies
 , single_date date NOT NULL
 , PRIMARY KEY (company_id, single_date)
);

Add an index on (single_date, company_id) as well. See:

• Is a composite index also good for queries on the first field?

Then your query can be:

SELECT c.*
FROM   companies c
WHERE  EXISTS (
   SELECT FROM company_date cd
   WHERE  single_date BETWEEN '2019-06-01' AND '2019-06-30'
   AND    cd.company_id = c.company_id
   );

Occupies more space on disk, more verbose query, but much faster for big tables and more versatile.