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?

Alexey Bashtanov · Accepted Answer · 2016-11-22 12:55:28Z

7

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'.

answered Nov 22, 2016 at 12:55

Alexey Bashtanov

1,40411 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Patrick Refondini Over a year ago

Works fine but I would avoid creating a function named as existing built in PostgreSQL functions postgresql.org/docs/current/…

mb21 Over a year ago

is such a to_json function that returns NULL on error instead of throwing still not built into psql as of 2024?

Collectives™ on Stack Overflow

Catch PostgreSQL exception and change column value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related