On Thursday, August 1, 2024, Tom Lane <
tgl@sss.pgh.pa.us> wrote:
Maciek Sakrejda <maciek@pganalyze.com> writes:
> Oddly, it looks like you only get a null if you use the '->>'
> operator. With '->' and a subsequent cast to text, you get the string
> "null":
> maciek=# select (('{"a":null}'::jsonb)->'a')::text;
> text
> ------
> null
> (1 row)
> Is that expected?
I think what is happening there is you're getting the fallback
"cast via I/O" behavior. There's no jsonb->text cast function
in the catalogs.
Perhaps it's worth adding one, so that it can be made to behave
similarly to the casts to other types.
I’m not too keen on opening Pandora’s box here even if I do regret our current choices. Semantic casting of json scalar strings only, and doing document serialization as a function, would have been better in hindsight.
I am fine with implementing the conversion of json null types to SQL null for all casts that already do semantic value casting, and thus recognize but prohibit the cast, as shown for float.
I read the discussion thread [1] that added this and while one person mentioned json null no one replied to that point and seemingly no explicit consideration for treating json null semantically was ever done - i.e. this fails only because in json null has its own type, and the test were type, not value, oriented. As SQL null is a value only, whose type is whatever holds it, I’d argue our lack of doing this even constitutes a bug but wouldn’t - and turning errors into non-errors has a lower “bug acceptance threshold”.
David J.
