I am trying to use the unnest optimisation to bundle rows into a single insert and reduce the number of RTTs to the database.
My basic insert query looks like so:
INSERT INTO table (fielda, fieldb, fieldc)
SELECT fielda, fieldb, fieldc
FROM unnest($1, $2, $3)) as a (fielda, fieldb, fieldc)
And this works beautifully, so long as fieldc is a scalar value.
If fieldc happens to be a real[] for example, and thus $3 is a real[][], this no longer works because unnest unnests to reals, not real[]s.
Somehow I need to get unnest to produce rows, only unnested by 1 level. There is a very helpful existing answer here: https://stackoverflow.com/a/8142998/15531756 which implements a function for unnesting a single array, but this creates a set of values, rather than a set of rows containing everything I need. As far as I am aware I can't join this array onto the normal unnest for the other parameters.
So how would one insert bundled data like this where a column has an array value type?
At the moment I have resorted to dynamically generating a query
INSERT INTO table (fielda, fieldb, fieldc)
VALUES
($1, $2, $3),
($4, $5, $6)
... and so on
but this is markedly slower, even re-using the same statement.
