Decompose a structure array into one-dimensional arrays

[igkg]

This article talks about the advantages of passing one-dimensional arrays and combining them on the PG side via unnest. But it is inconvenient to collect one-dimensional arrays on the application side. It is convenient to put them into a structure and then put it into an array. Is it possible to come up with something to easily get single arrays for passing from an array of structures? Or pass an array of structures to the pgx driver function, and it will cut it into single arrays itself.

[jackc]

In most cases, COPY should be preferred as it provides the best performance. If COPY cannot be used, such as when ON CONFLICT is needed then pgx's batch functionality should be preferred as that works with ON CONFLICT and provides feedback about what happened to each row.

It is unusual that this array / unnest pattern is the optimal approach and it's unlikely pgx will include helpers for this specific use case.

[igkg]

@jackc
It is not just about inserting, it also makes sense for updating, merging and deleting from select + unnest.

[igkg]

It turns out that they already tried to push this through at the compiler level. Oh, how much usefulness would come out of it.