Pedro Carvalho Pedro Carvalho - 4 months ago 37
JSON Question

Catch PostgreSQL exception and change column value

I have a column called

which has format
but contains stuff that's usually a JSON. I want to select a certain value in it (
metadata :: JSON -> 'register' ->> 'date'
for instance) but if the JSON is badly formatted or if the field doesn't exist I get an error. Can I make that column just return a
value when it would get an error instead?


You can write a stored procedure that tries to cast to json and returns null of failure. Something like this:

create function to_json(t text) returns json as $$
  return t::json;
exception when invalid_text_representation then
  return null;
$$ language plpgsql;

and then use to_json(metadata) -> 'register' ->> 'date'.