On Sun, Jul 5, 2026 at 8:59 AM Pavel Stehule <[email protected]> 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/
