.NET Npgsql用unnest实现PostgreSQL批量插入jsonb列失败如何解决

问题原因 #

你遇到的报错和后续数据异常都是参数类型配置错误导致的：

1. 最初报错42883: function pg_catalog.unnest(jsonb) does not exist的原因：你给@c参数指定的类型是单个NpgsqlDbType.Jsonb，Npgsql会把你传入的字符串数组整体序列化为单个JSON数组值，而非PostgreSQL识别的jsonb[]数组类型，传给unnest的参数是单个jsonb值，自然找不到对应的重载函数。
2. 改写成ARRAY[@c]后仅首行有值的原因：你把单个jsonb值用ARRAY()包成了只有1个元素的数组，unnest展开后只有第一行能匹配到这个元素，剩下的行对应不到元素就返回null。

解决方法 #

1. 修改参数类型配置 #

把jsonb参数的类型指定为jsonb数组，而非单个jsonb：

// 原来的错误写法
// new NpgsqlParameter<string[]>(name, NpgsqlTypes.NpgsqlDbType.Jsonb) { Value = value.ToJson() }

// 修改后的正确写法
new NpgsqlParameter<string[]>(name, NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Jsonb) 
{ 
    Value = value.ToJson() 
}

bar列对应的@d参数也按同样规则配置即可。

2. 改回原SQL语句 #

不需要额外加ARRAY()包裹参数，直接用最初的写法即可正常批量插入：

INSERT INTO data_table2 (id, name, test, bar) SELECT * FROM unnest(@a, @b, @c, @d)

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