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?
The "problem" is that the ANY construct works for operators, not for other constructs - and BETWEEN is another construct.
Related:
There is an easy solution for this, though. Construct a daterange from the given bounds and use the contains operator @>.
Related:
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:
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.
