Re: IS JSON STRICT - In oracle => postgres ??

Am 14.06.23 um 12:00 schrieb Nikhil Ingale:
> Hi All,
> In oracle we can use the *is json* check constraint for the json
> column to ensure the data is well formed.
>
> How do we ensure the same in postgres database?
>
> Regards,
> Nikhil Ingale

Hi Nikhil,

If you insist that you want to check without putting the content into a
JSON(B) column, you might try this function:

|create or replace function is_valid_json(p_json text) returns boolean
as $$ begin return (p_json::json is not null); exception when others
then return false; end; $$ language plpgsql immutable;|

https://stackoverflow.com/questions/30187554/how-to-verify-a-string-is-valid-json-in-postgresql

It just tries to cast the text to JSON. If it can be casted, it's valid
JSON, otherwise it's not.

Regards,

Holger

--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012