psql bluk insert array column with different length

Question

The unnest work fine for no array data. I want to bluk insert an array column. My input like this: [[1, 2], [1]]

create table test_array
(
    arr int[],
    info text[]
);

CREATE OR REPLACE FUNCTION unnest_2d_1d(anyarray)
RETURNS SETOF anyarray AS
$func$
SELECT array_agg($1[d1][d2])
FROM   generate_subscripts($1,1) d1
    ,  generate_subscripts($1,2) d2
GROUP  BY d1
ORDER  BY d1
$func$  LANGUAGE sql IMMUTABLE;

insert into test_array(
arr)
values (
unnest_2d_1d(array[[1,2], [3,4]]::int[]));  -- work fine


insert into test_array(
arr)
values (
unnest_2d_1d(array[[1,2], [3]]::int[]));   -- will failed

The failed error is :

 ERROR: multidimensional arrays must have array expressions with matching dimensions

unnest_2d_1d come from Unnest array by one level While all such function can't support different length of array.

Another same error happened with array_dims:

postgres=# SELECT array_dims(ARRAY[[1,2,3], [4,5,6], [7,8]])
postgres-# ;
ERROR:  multidimensional arrays must have array expressions with matching dimensions

What I need to do with unnest_2d_1d to make the failed one can be success?

Why I must do with unnest. Because asyncpg execute_many discard the returning values. So I need use one sql to insert data. But the array column is such like one dim with different length.

---- add some python and asyncpg

I find a github issue related this problem, https://github.com/brianc/node-postgres/issues/1644 try to do with python

When try this :

async def h():
    async with misc.pg_pool.acquire() as connection:
        stmt = await connection.prepare(f"""
            insert into test_array(info)  select (info::text[]) from  unnest($1::text[])
            AS t(info)
        """)
        await stmt.fetch([["sd", "dg"]])

got this

InvalidTextRepresentationError: malformed array literal: "sd"
DETAIL:  Array value must start with "{" or dimension information.

Answer 1

Ok.

This will work:

insert into test_array(info) 
        select (info::text[]) from  unnest(array['{"sd", "dg"}', '{"g"}']::text[]) as t(info)

This will work too:

insert into test_array(info) 
        select info from jsonb_to_recordset('[{"info":["ttt"]}, {"info":["tt","t"]}]'::jsonb) as t(info text[])