Incorrect behavior of cs. @> in daterange

[govidat]

Environment

• PostgreSQL version: 12
• PostgREST version: 7.0.0
• Operating system: Debian

Description of issue

I have a daterange field (eg validity) and am trying to get if current_date is in the daterange.

As per postgrest documentation , we need to use: cs - contains e.g. ?tags=cs.{example, new}
Two issues are noticed:

1. cs. works on [ ] and not on {} ?tags=cs.[from_date, to_date]
2. It expects a range to compare, while postgres takes a single date value to compare.

(Steps to reproduce: Include a minimal SQL definition plus how you make the request to PostgREST and the response body)
In pg the following select works correctly:
select * from mytable WHERE validity @> '2020-07-21'::date;
or
select * from mytable WHERE validity @> current_date;

Whereas, with postgrest it works only on cs.[from_date, to_date].

[steve-chavez]

Docs definitely need clarification. The @>(cs) works both for array and range types. That's why the {} (array literal) notation is mentioned in some parts and others have [)(range literal).

Workaround:

# in sql
select * from mytable WHERE validity @> '[2020-07-21,2020-07-21]';

# in postgrest
curl 'localhost:3000/mytable?validity=cs.\[2020-07-21,2020-07-21\]'

Not ideal to duplicate the value, but it should work.

[steve-chavez]

Should we parse the cs operand(cs.2020-07-21) to allow accepting a single value as well?

Seems possible. If no { or [ is detected, then we cast the value.

It's kind of related to the proposal in #1569 (comment). We may have to think if we should offer these facilities for better UX.

[SimonJohnCastle]

I confirm Steve's solution cs.[date,date] above works, is it planned to add the single date syntax of some kind

[wolfgangwalther]

Should we parse the cs operand(cs.2020-07-21) to allow accepting a single value as well?

Hm, I'm not sure about that. This seems to only work for a very special case - i.e. if your column in date. Once the column is timestamp(tz), this won't make sense anymore, it won't do what you expect it to do.

I recently had to do a lot of things around filtering for dates.. and no matter what, you'll need to invest a bit of time to understand it as an API user. I don't think we can make this easy magically.