0

I was wondering how to address the tsrange problem in postgres, as tsrange('2010-01-01 14:30', '2010-01-01 15:30')::json returns annoying escapes: "[\"2010-01-01 14:30:00\",\"2010-01-01 15:30:00\")". Postgres REST nicely suggest to approach the problem through automatic casts:

create or replace function tsrange_to_json(tsrange) returns json as $$
  select json_build_object(
    'lower', lower($1)
  , 'upper', upper($1)
  , 'lower_inc', lower_inc($1)
  , 'upper_inc', upper_inc($1)
  );
$$ language sql;
create cast (tsrange as json) with function tsrange_to_json(tsrange) as assignment;

which works nicely if I cast tsrange::json but keeps the old formatting if I do to_json(tsrange)

select tsrange('2010-01-01 14:30', '2010-01-01 15:30')::json
-- "{"lower" : "2010-01-01T14:30:00", "upper" : "2010-01-01T15:30:00", "lower_inc" : true, "upper_inc" : false}"
select to_json(tsrange('2010-01-01 14:30', '2010-01-01 15:30'))
-- ""[\"2010-01-01 14:30:00\",\"2010-01-01 15:30:00\")""

Why to_json does not use ::json casts?

arthur
  • 888
  • 3
  • 13
  • 25
  • 1
    Because to_json() is not a cast. It's a conversion function similar to to_date() or to_char() –  Jul 01 '20 at 09:01
  • @a_horse_with_no_name: thanks for the hint. Is there any way to expand on your answer in https://dba.stackexchange.com/a/200240/28367 to elegantly solve to_jsonb(b) - 'item_id' where b contains tsranges. Because now it just makes strings inside with lots of escapes? – arthur Jul 01 '20 at 09:11

0 Answers0