Range operators cannot be used inside or without quoting

[steve-chavez]

Problem

This works:

curl -g 'localhost:3000/ranges?range=adj.(3,10]&select=id'
[{"id":1}]

This doesn't:

curl -g 'localhost:3000/ranges?or=(range.adj.[3,10))&select=id'

{"code":"PGRST100","details":"unexpected \")\" expecting letter, digit, \"-\", \"->>\", \"->\" or delimiter(.)","hint":null,"message":"\"failed to parse logic tree ((range.adj.[3,10)))\" (line 1, column 19)"}

curl -g 'localhost:3000/ranges?or=(range.adj.(3,10])&select=id'

{"code":"PGRST100","details":"unexpected \"]\" expecting letter, digit, \"-\", \"->>\", \"->\" or delimiter (.)","hint":null,"message":"\"failed to parse logic tree ((range.adj.(3,10]))\" (line 1, column 19)"}

Possible Solutions

• or syntax without values: Swagger: GET record from table with uuid primary key: failed to parse filter #1970 (comment)
• the SEARCH method to parametrize the values Implement new SEARCH/QUERY HTTP method to pass arguments in request body for read request #2125 (comment)

[wolfgangwalther]

Did you try to quote the value instead?

curl -g 'localhost:3000/ranges?or=(range.adj."[3,10)")&select=id'

[steve-chavez]

Works!

curl -g 'localhost:3000/ranges?or=(range.adj."(3,10]")&select=id'

[{"id":1}]

I guess still a bug though.

[wolfgangwalther]

I guess still a bug though.

Huh? It works as designed. I really don't see the bug.

[steve-chavez]

Right, maybe this is just a matter of docs then. It would be nice to have a mention about this gotcha on https://postgrest.org/en/v12/references/api/tables_views.html#operators

[wolfgangwalther]

I think it should be in the next section, so not operators, but logical operators: https://postgrest.org/en/v12/references/api/tables_views.html#logical-operators

[laurenceisla]

Maybe this is related and should be added to the docs too. From this discussion #3466 (comment):

curl --globoff 'http://example.com/table?select=*&and=(column->jsonlist.cs.["foo=()"]'

results in

{"code":"22P02","details":"Token \"\"foo=(\" is invalid.","hint":null,"message":"invalid input syntax for type json"}

The solution is to wrap the whole JSON array value inside quotations, (not just the items). That is:

curl --globoff 'http://example.com/table?select=*&and=(column->jsonlist.cs."[\"foo=()\"]"'