Re: inconsistent jsonb 'null' conversions

ne 5. 7. 2026 v 17:38 odesílatel Sehrope Sarkuni <sehrope(at)jackdb(dot)com>
napsal:

> On Sun, Jul 5, 2026 at 11:13 AM Sehrope Sarkuni <sehrope(at)jackdb(dot)com>
> wrote:
> > On Sun, Jul 5, 2026 at 8:59 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > >
> > > I found inconsistency in casting the value "null" of jsonb type to
> scalar types.
> > >
> > > Is it expected behaviour?
> >
> > I'm not sure if this is expected, but the cast from json to int not
> > giving an error for null is a change from v17. That cast previously
> > failed.
>
> Thinking about this a bit more, it does make sense and I'd say its
> expected behavior.
>
> The cast from jsonb (or json) to text is not extracting the one value.
> It's representing the entirety of any json/jsonb and would allow round
> tripping back again. It's effectively JSON.stringify(...) of the
> value. Otherwise you would not be able to represent null vs "null".
>
> The cast to int is for a specific scalar value and only works when the
> value itself is a number. It does not allow for transforming JSON
> strings, just null and numbers:
>
> SELECT '"123"'::jsonb::int
> ERROR: cannot cast jsonb string to type integer
>
> SELECT '123'::jsonb::int
> int4
> ------
> 123
> (1 row)
>
> SELECT 'null'::jsonb::int IS NULL;
> ?column?
> ----------
> t
> (1 row)
>
> So I'd say it's correct because the null int is the proper value to
> match the null jsonb scalar.
>

Yes, it is possible to see some logic there

Thank you for the reply

Regards

Pavel