PostgreSQL插入长度不一致二维数组报错如何解决？

PostgreSQL 原生多维数组遵循矩形数组约束，要求同一维度下的所有子数组长度必须完全相同，你尝试插入的是长度不一致的「锯齿数组」，不符合原生多维数组的格式要求，所以触发字面量格式错误。

方案1：调整字段类型为元素是数组的一维数组 #

不要将字段声明为二维数组text[][]，直接声明为通用text[]类型即可支持存储不同长度的子数组，不会触发长度对齐校验：

调整后建表语句 #

CREATE TABLE test (
    field1 text[]
);

插入语句写法 #

使用ARRAY构造器显式构造每个子数组：

INSERT INTO test VALUES (ARRAY[ARRAY[]::text[], ARRAY['foo'], ARRAY['foo', 'bar']]);

验证查询 #

使用逻辑和原有需求完全一致：

-- 提取第二个子数组，返回 {foo}
SELECT field1[2] FROM test;
-- 提取第三个子数组的第二个元素，返回 bar
SELECT field1[3][2] FROM test;

方案2：使用JSONB类型存储 #

如果后续需要更灵活的嵌套结构扩展，可直接用jsonb类型存储不规则数组：

建表语句 #

CREATE TABLE test (
    field1 jsonb
);

插入语句 #

INSERT INTO test VALUES ('[[], ["foo"], ["foo", "bar"]]');

操作示例 #

-- 提取第二个元素，返回 ["foo"]
SELECT field1 -> 1 FROM test;
-- 提取第三个元素的第二个值，返回字符串类型的 bar
SELECT field1 -> 2 ->> 1 FROM test;

该方案支持任意层级的不规则嵌套，还可通过JSONB索引优化查询性能。

方案3：填充空值对齐子数组长度 #

如果你必须保留原生二维数组的类型，可在业务层将短子数组用null填充到和最长子数组相同的长度，满足矩形约束：

INSERT INTO test VALUES ('{{null, null}, {foo, null}, {foo, bar}}');

该方案无需修改表结构，查询时需要自行过滤填充的null值即可。

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