pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIS JSON STRICT - In oracle => postgres ??
7+ messages / 6 participants
[nested] [flat]
* IS JSON STRICT - In oracle => postgres ??
@ 2023-06-14 10:00 Nikhil Ingale <niks.bgm@gmail.com>
0 siblings, 4 replies; 7+ messages in thread
From: Nikhil Ingale @ 2023-06-14 10:00 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: IS JSON STRICT - In oracle => postgres ??
@ 2023-06-14 10:15 hubert depesz lubaczewski <depesz@depesz.com>
parent: Nikhil Ingale <niks.bgm@gmail.com>
3 siblings, 0 replies; 7+ messages in thread
From: hubert depesz lubaczewski @ 2023-06-14 10:15 UTC (permalink / raw)
To: Nikhil Ingale <niks.bgm@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Wed, Jun 14, 2023 at 03:30:50PM +0530, Nikhil Ingale wrote:
> 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?
Well, if the data is in json(b) column, then it is well formed, isn't
it?
Best regards,
depesz
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: IS JSON STRICT - In oracle => postgres ??
@ 2023-06-14 14:09 Holger Jakobs <holger@jakobs.com>
parent: Nikhil Ingale <niks.bgm@gmail.com>
3 siblings, 1 reply; 7+ messages in thread
From: Holger Jakobs @ 2023-06-14 14:09 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: IS JSON STRICT - In oracle => postgres ??
@ 2023-06-14 15:03 Thomas Kellerer <shammat@gmx.net>
parent: Nikhil Ingale <niks.bgm@gmail.com>
3 siblings, 0 replies; 7+ messages in thread
From: Thomas Kellerer @ 2023-06-14 15:03 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Nikhil Ingale schrieb am 14.06.2023 um 12:00:
> 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?
That check constraint isn't necessary in Postgres.
A column defined as jsonb (or json) will not accept invalid JSON.
That's pretty much the behaviour that's introduced in Oracle 23 with the new JSON data type.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: IS JSON STRICT - In oracle => postgres ??
@ 2023-06-16 08:05 jian he <jian.universality@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 0 replies; 7+ messages in thread
From: jian he @ 2023-06-16 08:05 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
On Wed, Jun 14, 2023 at 10:09 PM Holger Jakobs <holger@jakobs.com> wrote:
> 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 booleanas
> $$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
>
>
https://pgpedia.info/p/pg_input_is_valid.html
in postgres 16, you can try
-------------------------------
with cte(x) as (select '{"hello":"world","hello":"yes"}')
select pg_input_is_valid(x,'jsonb') from cte;
---------------------------
It just tries to cast the text to JSON. If it can be casted, it's valid
> JSON, otherwise it's not.
correct me if I am wrong, seems null corner case not handled?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: IS JSON STRICT - In oracle => postgres ??
@ 2023-06-20 08:07 Peter Eisentraut <peter@eisentraut.org>
parent: Nikhil Ingale <niks.bgm@gmail.com>
3 siblings, 1 reply; 7+ messages in thread
From: Peter Eisentraut @ 2023-06-20 08:07 UTC (permalink / raw)
To: Nikhil Ingale <niks.bgm@gmail.com>; pgsql-admin@lists.postgresql.org
On 14.06.23 12:00, Nikhil Ingale wrote:
> 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?
PostgreSQL 16 has an IS JSON predicate.
It doesn't have a STRICT clause, which I don't see in the SQL standard.
So it might not do exactly the same thing.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: IS JSON STRICT - In oracle => postgres ??
@ 2023-06-20 08:57 Nikhil Ingale <niks.bgm@gmail.com>
parent: Peter Eisentraut <peter@eisentraut.org>
0 siblings, 0 replies; 7+ messages in thread
From: Nikhil Ingale @ 2023-06-20 08:57 UTC (permalink / raw)
To: Peter Eisentraut <peter@eisentraut.org>; +Cc: pgsql-admin@lists.postgresql.org
Information from you guys helped to resolve the issue.
Thank you all for the valuable inputs.
On Tue, Jun 20, 2023 at 1:37 PM Peter Eisentraut <peter@eisentraut.org>
wrote:
> On 14.06.23 12:00, Nikhil Ingale wrote:
> > 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?
>
> PostgreSQL 16 has an IS JSON predicate.
>
> It doesn't have a STRICT clause, which I don't see in the SQL standard.
> So it might not do exactly the same thing.
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-06-20 08:57 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-14 10:00 IS JSON STRICT - In oracle => postgres ?? Nikhil Ingale <niks.bgm@gmail.com>
2023-06-14 10:15 ` hubert depesz lubaczewski <depesz@depesz.com>
2023-06-14 14:09 ` Holger Jakobs <holger@jakobs.com>
2023-06-16 08:05 ` jian he <jian.universality@gmail.com>
2023-06-14 15:03 ` Thomas Kellerer <shammat@gmx.net>
2023-06-20 08:07 ` Peter Eisentraut <peter@eisentraut.org>
2023-06-20 08:57 ` Nikhil Ingale <niks.bgm@gmail.com>
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