为什么将JSONB NULL转换为指定类型会失败？这是PostgreSQL的规范缺陷吗？

这个问题的本质是PostgreSQL对JSON类型和SQL原生类型的语义隔离设计，并非规范缺陷。

核心原因 #

• 你遇到的报错是因为 -> 运算符返回的是jsonb类型值，此处拿到的 jsonb 'null' 是JSON语义里的空值，属于合法的非SQL-NULL的jsonb对象，和SQL层面的NULL是完全两个不同的概念
• 如果默认将jsonb null强制转换为对应SQL类型的NULL，会直接导致两种场景无法区分：
  1. JSON中不存在目标键，返回SQL NULL
  2. JSON中存在目标键，但值为JSON null，返回jsonb 'null'
     这种歧义会给需要明确区分两种场景的业务逻辑带来不可预期的故障，因此PostgreSQL默认不会做隐式转换。

符合直觉的解决方案 #

不需要额外写CASE语句处理，直接使用返回text类型的->>运算符即可，它会自动将JSON null转换为SQL NULL，再做类型转换就不会报错：

SELECT (j->>'i')::int FROM  (SELECT '{"i":null}'::jsonb) t(j); -- 返回NULL，无报错

内容的提问来源于stack exchange，提问作者Peter Krauss