Re: inconsistent jsonb 'null' conversions

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.
>
> (2026-07-05 14:54:08) postgres=# select 'null'::jsonb::int is null;
> ┌──────────┐
> │ ?column? │
> ╞══════════╡
> │ t │
> └──────────┘
> (1 row)
>
> (2026-07-05 14:56:26) postgres=# select 'null'::jsonb::varchar is null;
> ┌──────────┐
> │ ?column? │
> ╞══════════╡
> │ f │
> └──────────┘
> (1 row)
>
> 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.

The json-to-int (as opposed to jsonb) fails for both v17 and v18. Also
interesting that the json-to-int provides the error location but
jsonb-to-int does not.

SELECT version()
version
--------------------------------------------------------------------------------------------------------------------
PostgreSQL 18.3 (Debian 18.3-1.pgdg13+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)

SELECT 'null'::jsonb::int IS NULL
?column?
----------
t
(1 row)

SELECT 'null'::json::int IS NULL
ERROR: cannot cast type json to integer
LINE 1: SELECT 'null'::json::int IS NULL
^
SELECT 'null'::json::text IS NULL
?column?
----------
f
(1 row)

SELECT version()
version
---------------------------------------------------------------------------------------------------------------------
PostgreSQL 17.5 (Debian 17.5-1.pgdg120+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

SELECT 'null'::jsonb::int IS NULL
ERROR: cannot cast jsonb null to type integer

SELECT 'null'::json::int IS NULL
ERROR: cannot cast type json to integer
LINE 1: SELECT 'null'::json::int IS NULL
^
SELECT 'null'::json::text IS NULL
?column?
----------
f
(1 row)

Regards,
-- Sehrope Sarkuni
Founder & CEO | JackDB, Inc. | https://www.jackdb.com/