Catch PostgreSQL exception and change column value

Question

I have a column called metadata which has format TEXT 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 NULL value when it would get an error instead?

Answer 1

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 $$
begin
  return t::json;
exception when invalid_text_representation then
  return null;
end;
$$ language plpgsql;

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