PostgreSQL malformed array literal异常

现象

在一个存储过程中编写如下代码（省略与本异常无关的代码）：

declare
hbsn_arr varchar(240)[];
#bddm.hbsn 内容类似于'{"chain0":["NULL"],"chain1":["FESDF09402342","NULL"],...}'
for chain_name, hbsn_arr inselect key as chain_name, value as hbsn_arr from jsonb_each(bddm.hbsn) order by key ascloop#some statementend loop;

执行代码过程中出现如下异常：

SQL 错误 [22P02]: ERROR: malformed array literal: "["NULL"]"详细："[" must introduce explicitly-specified array dimensions.在位置：PL/pgSQL function make_hashboard_v() line 31 at FOR over SELECT rows

原因

jsonb_each返回的value值’[“NULL”]‘与’[“FESDF09402342”,“NULL”]‘不能装进varchar(240)[]这样定义的PostgreSQL 数组类型，PostgreSQLl数组标量应该要"{‘NULL’,‘NULL’}"或者"ARRAY[‘NULL’,‘NULL’]"这样定义,参考《PostgreSQL数据库的array类型》这篇文章。而jsonb_each返回的value结构为’[“NULL”，“NULL”]'，不匹配PostgreSQL数组类型所需的标量格式。

而jsonb的数组标量定义为 ‘[“NULL”]’，两者不同。

处理

hbsn_arr的类型定义修改为varchar(后续再转jsonb)或jsonb。