search

PostgREST / postgrest

REST API for any Postgres database
https://postgrest.org
MIT License
23.21k stars 1.02k forks source link

Incorrect behavior of cs. @> in daterange #1563

Open govidat opened 4 years ago

govidat commented 4 years ago

Environment

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 commented 4 years ago

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 commented 4 years ago

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 https://github.com/PostgREST/postgrest/issues/1569#issuecomment-673610581. We may have to think if we should offer these facilities for better UX.