2

As commented in this answer,

SELECT (j->'i')::int FROM  (SELECT '{"i":null}'::jsonb) t(j); -- fail

results in "ERROR: cannot cast jsonb null to type integer"... Ok, this is the "PostgreSQL way", but why not make it better? Better than add CASE clauses, it is doing the "natural" thing, that is casting a JSON-NULL value to a SQL typed null value. So, it is not an implementation problem, but it seems a specification bug in PostregSQL: it is?

Peter Krauss
  • 13,174
  • 24
  • 167
  • 304
  • [Table 8.23. JSON Primitive Types and Corresponding PostgreSQL Types](https://www.postgresql.org/docs/current/datatype-json.html#JSON-TYPE-MAPPING-TABLE) in the documentation does mention that "json null" has no postgresql type equivalent with a comment saying "SQL NULL is a different concept", so my guess is that this is indeed the desired behavior. Though I agree it does seem like this would natural/convenient. You can use `(j->>'i')::int` instead, as `->>'i'` will return a `text`, which can be cast to `int` (and return `null` in this case). – Marth Nov 16 '21 at 20:11
  • https://stackoverflow.com/q/60163598/2650437 might be of interest too. – Marth Nov 16 '21 at 20:19
  • Same thing if you did `('[]'::jsonb)::int` or `('{}'::jsonb)::int` or `('"1"'::jsonb)::int` - they are JSON values that cannot be converted to an `int` value. You'll get an exception, not `NULL`. Only `(NULL::jsonb)::int` will become an integer `NULL`. – Bergi Nov 16 '21 at 20:43
  • 1
    For starters take a look at this thread [Jsonb strictness](https://www.postgresql.org/message-id/CAAOiGNwUsgM-UBqsJH_mriCFr-JCPxD%2BpQoXNvyOEQPc%2BYHQRQ%40mail.gmail.com). Then search the same list for `json null`. Bottom line many people did not want mixing of json null and SQL NULL. – Adrian Klaver Nov 16 '21 at 22:17

1 Answers1

4

The reason is that SQL NULL is quite different from JSON null.

If you want to do this:

SELECT nullif((j->'i'), 'null')::int FROM  (SELECT '{"i":null}'::jsonb) t(j);
 nullif 
--------
   NULL
(1 row)

 SELECT pg_typeof(nullif((j->'i'), 'null')::int) FROM  (SELECT '{"i":null}'::jsonb) t(j);
 pg_typeof 
-----------
 integer


SELECT nullif((j->'i'), 'null')::int FROM  (SELECT '{"i": 1}'::jsonb) t(j);
 nullif 
--------
      1
(1 row)

Use NULLIF to transform JSON null to SQL NULL.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
  • Hi, it looks like a **specification bug**: all JSON data types are quite different from SQL, maybe only JSON-string to SQL-text is straightforward (no transformation only a "datatype rename"). All other CASTs, **to be frindly** and orthogonal, PostgreSQL offers a kind of data transformatin. For example JSON number CAST to SQL-FLOAT is a transformation. So JSON-NULL to SQL-NULL can be a transformation. – Peter Krauss Sep 22 '22 at 10:31
  • It is not a specification bug. Read the link [Jsonb strictness](https://www.postgresql.org/message-id/CAAOiGNwUsgM-UBqsJH_mriCFr-JCPxD%2BpQoXNvyOEQPc%2BYHQRQ%40mail.gmail.com) I posted in my comment above. Also look at [JSON](https://www.postgresql.org/docs/current/datatype-json.html) *Table 8.23. JSON Primitive Types and Corresponding PostgreSQL Types* "null (none) SQL NULL is a different concept". It is a deliberate choice. – Adrian Klaver Sep 22 '22 at 14:43
  • Yes, I am reinforcing that is a specification fail. When PostgreSQL-specification say **"different concept" it's a theoretical orientation, to be careful, but not to impose barriers (!)** on context-sensitive parsing. A CAST must be friendly... We (users) must review the "spec implementation" of this orientation. – Peter Krauss Sep 23 '22 at 11:22
  • Read the mailing list thread I linked to and search the [pgsql-general](https://www.postgresql.org/list/pgsql-general/) for 'json null'. You will find there was plenty of user input. There where arguments on both sides, but in the end this specification won out. At this point it will not be changed, too much code depends on the current behavior. – Adrian Klaver Sep 23 '22 at 14:37