Re: inconsistent jsonb 'null' conversions

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.