Issue with Posting Date range (Tsrange) with Json

[Mjaffres]

Environment

• PostgreSQL version: 12
• PostgREST version: 7.0.1
• Operating system: ubuntu 20.04 LTS
  nginx 1.19

Description of issue

I don't really think it's a bug but more me that doesn't find how it works ( I am new to this)

(Expected behavior vs actual behavior)

I want to Post a Json object with a daterange (tsrange in postgresql)
I use request with a json body and try a text expression of the date range
for me it should work but I always catch an error with message "malformed range literal:"

(Steps to reproduce: Include a minimal SQL definition plus how you make the request to PostgREST and the response body)

basic table in PostgreSql

create table ___.transformation(
    id varchar primary key,
    type_ ___.transformation_type not null,
    source ___.station_type,
    parent varchar references ___.transformation(id),
        check ((source is not null and parent is null) or (source is null and parent is not null)),
    station varchar not null references ___.station(id) on update cascade on delete restrict,
    period tsrange not null,
    author varchar default session_user,
    creation_date timestamp default now()::timestamp)
;

// api 
create view api.transformation_time_translation as
select t.id, t.type_, t.source, t.parent, t.station, t.period, t.author, t.creation_date, tt.offset_ 
from ___.transformation_time_translation tt
join ___.transformation t on t.id = tt.id
;
alter view api.transformation_time_translation alter id set default uuid_generate_v4()::varchar
;
alter view api.transformation_time_translation alter creation_date set default now()::date
;
alter view api.transformation_time_translation alter author set default session_user
;
create function api.transformation_time_translation_instead_fct()
returns trigger
language plpgsql as
$$
begin
    if tg_op = 'INSERT' then
        insert into ___.transformation(id, type_, source, parent, station, period, author, creation_date)
            values(new.id, 'time translation'::___.transformation_type, new.source, new.parent, new.station, new.period, new.author, new.creation_date);
        insert into ___.transformation_time_translation(id, offset_)
            values (new.id, new.offset_);
        return new;
    elsif tg_op = 'UPDATE' then
        update ___.transformation_time_translation set id=new.id, source=new.source, parent=new.parent, station=new.station, period=new.period, author=new.author, creation_date=new.creation_date
            where id=old.id;
        update ___.transformation_time_translation set offset_=new.offset_
            where id=old.id;
        return new;
    elsif tg_op = 'DELETE' then
        delete from ___.transformation_time_translation where id=old.id;
        delete from ___.transformation where id=old.id;
        -- TODO relink transfo (reparenting)
        return old;
    end if;
end;
$$

Js part to make the post request

transformation = {};
transformation["type_"] = type_transfo;
transformation["station"] = id;
transformation["source"] = type_;
// syntax I tried
// transformation["period"] = `'{[2014-01-01,2015-01-01]}'`;
// transformation["period"] = `'{\"[\"2014-01-01 00:00:00+01\",\"2015-01-01 00:00:00+01\"]\"}'`;
// transformation["period"] = \"{\"[\"2014-01-01 00:00:00+01\",\"2015-01-01 00:00:00+01\"]\"}\";
// transformation["period"] = `'{"[\"`+ date_start +`\",\"`+ date_end +`\"]"}'`;
transformation["offset_"] = "interval '" + document.getElementById("offset_time_translation").value +' minutes';

fetch('/api/transformation_'+type_transfo, {
        method:'POST', 
        headers:new Headers({"Content-Type":"application/json", "Authorization": "Bearer "+token}), 
        body: JSON.stringify(transformation)
        })
        .then(function(response) {
            console.log(response.json())
        if (response.status != 201){
            alert(response.statusText);
        }
        reload();
        })

my issue is pretty simple :
I can't find a proper text formatting to insert a tsrange trough the Json with the Post Query

I end up with

: Object { details: "Missing left parenthesis or bracket.", code: "22P02", message: "malformed range literal: "'{"["2014-01-01 00:00:00+01","2015-01-01 00:00:00+01"]"}'"", … }

malformed range literal whenever I try something.

So either I shouldn't pass a text argument in the Json for doing this but I can't find any doc or syntax for this case.
any help or explanation appreciated.
Thanks

[wolfgangwalther]

// transformation["period"] = `'{[2014-01-01,2015-01-01]}'`;
// transformation["period"] = `'{\"[\"2014-01-01 00:00:00+01\",\"2015-01-01 00:00:00+01\"]\"}'`;
// transformation["period"] = \"{\"[\"2014-01-01 00:00:00+01\",\"2015-01-01 00:00:00+01\"]\"}\";
// transformation["period"] = `'{"[\"`+ date_start +`\",\"`+ date_end +`\"]"}'`;

: Object { details: "Missing left parenthesis or bracket.", code: "22P02", message: "malformed range literal: "'{"["2014-01-01 00:00:00+01","2015-01-01 00:00:00+01"]"}'"", … }

malformed range literal whenever I try something.

All those have the curly brackets in common - which is exactly what I think is wrong. As you can see from the error message, the curly brackets make it all the way to postgresql - but the docs on range types don't mention any of those: https://www.postgresql.org/docs/current/rangetypes.html.

I suggest you start with transformation["period"] = "[2014-01-01,2015-01-01]";, and then work your way up from there.

[Mjaffres]

ok, thanks for your answer
I did try the simple syntax :
text json

{"type_":"time translation","station":"yotest","source":"network height","period":"[2014-01-01,2015-01-01]","offset_":"'27 minutes'"}

but this give a Json parse error

Promise { : "pending" }
​
: "rejected"
​
: SyntaxError: JSON.parse: unexpected end of data at line 1 column 1 of the JSON data

I suspect the comma in "period":"[2014-01-01,2015-01-01]" to be the problem here

[wolfgangwalther]

That parse error is probably just because you're doing console.log(response.json()), when the request was successful but returned an empty body. You're not setting any Prefer: return header, so the body will be empty.

[Mjaffres]

pff ...
as I said , pretty new to all this stuff.

Thanks a lot, my issue is solved.