Unsuccessful attempt to query in a date range using cd (<@)

[maca]

Environment

• PostgreSQL version: 13.2
• PostgREST version: 7.0.1
• Operating system: Linux (5.12.6-arch1-1)

Description of issue

I am trying to replicate this query:

select * from api.songs where songs.released <@ '[2000-01-01, 2001-01-01]'::daterange;

But I cannot get PostgREST to cast the operand, I've tried the following:

GET http://localhost:3000/songs?released=cd.'[1994-02-01,2022-03-01]'

GET http://localhost:3000/songs?released=cd.'\[1994-02-01,2022-03-01\]'

GET http://localhost:3000/songs?released=cd.'[1994-02-01,2022-03-01]::daterange'

This is the response:

{
  "hint": null,
  "details": null,
  "code": "42804",
  "message": "could not determine polymorphic type anyrange because input has type unknown"
}
// GET http://localhost:3000/songs?released=cd.'[1994-02-01,2022-03-01]'
// HTTP/1.1 400 Bad Request

[steve-chavez]

Hey @maca,

GET http://localhost:3000/songs?released=cd.'\[1994-02-01,2022-03-01\]'

Have you tried urlencoding the brackets? The request should be like:

GET http://localhost:3000/songs?released=cd.%5B1994-02-01,2022-03-01%5D

Also, what's the type of the released column? If it's a daterange then there should be no need to cast it.

[maca]

Thank you @steve-chavez :)

The column is a date, this query gives me the results I want:

select * from api.songs where songs.released <@ '[2000-01-01, 2001-01-01]'::daterange;

Is there any way to cast the operand %5B1994-02-01,2022-03-01%5D of the cd operation to a ::daterange?

I've tried url encoding but the result is the same:

{
  "hint": null,
  "details": null,
  "code": "42804",
  "message": "could not determine polymorphic type anyrange because input has type unknown"
}
// GET http://localhost:3000/songs?released=cd.'[1994-02-01,2022-03-01]'
// HTTP/1.1 400 Bad Request

[steve-chavez]

The column is a date
Is there any way to cast the operand %5B1994-02-01,2022-03-01%5D of the cd operation to a ::daterange?

Ah, I see why the error now. It's not possible to cast a filter, but you can create a computed column and do the casting inside, as suggested here.

Additionally, for this case I think you can just use the lte and gte operators.

-- should be equivalent to the <@ operation
select * from api.songs where songs.released >= '2000-01-01' and songs.released =< '2001-01-01';

-- then through the API
GET /songs?released=lte.2000-01-01&released=gte.2001-01-01

[mb00t]

Thank for all! I had to adjust the range

-- should be equivalent to the <@ operation
select * from api.songs where songs.released >= '2000-01-01' and songs.released =< '2001-01-01';

-- then through the API
GET /songs?released=lte.2000-01-01&released=gte.2001-01-01

Change lte. and gte. Example question database from asterisk - >> GET /astercdr?calldate=gte.2022-05-07&calldate=lte.2022-05-10&select=src,duration,calldate
it's work