pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: IS JSON STRICT - In oracle => postgres ??
Date: Wed, 14 Jun 2023 16:09:14 +0200
Message-ID: <776384e5-2480-e950-9390-3212f3033856@jakobs.com> (raw)
In-Reply-To: <CALXkTpx66eJKJM1YPFmYJNJwn2LeGAb7qkiXkhyOGBCoKA4isQ@mail.gmail.com>
References: <CALXkTpx66eJKJM1YPFmYJNJwn2LeGAb7qkiXkhyOGBCoKA4isQ@mail.gmail.com>

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



Attachments:

  [application/pgp-signature] OpenPGP_signature (202B, ../776384e5-2480-e950-9390-3212f3033856@jakobs.com/3-OpenPGP_signature)
  download

view thread (7+ messages)  latest in thread

Message-ID: <776384e5-2480-e950-9390-3212f3033856@jakobs.com>
Permalink:  ../776384e5-2480-e950-9390-3212f3033856@jakobs.com/
Also on:    postgresql.org/message-id/776384e5-2480-e950-9390-3212f3033856@jakobs.com

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: holger@jakobs.com, pgsql-admin@lists.postgresql.org
  Subject: Re: IS JSON STRICT - In oracle => postgres ??
  In-Reply-To: <776384e5-2480-e950-9390-3212f3033856@jakobs.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox