Add `insertBatch`?

[kolemannix]

I believe there is currently no way to perform a batch insert that returns generated values, due to the fact that insertStreaming uses the COPY API. This is great except for cases where the generated values are required by the application. Running a series of plain inserts wrapped in a ConnectionIO.sequence performs far worse than a single insert ... values ... statement.

upsertBatch is close in terms of API shape but doesn't quite work for this case because doesn't accept an Unsaved row.

[oyvindberg]

Yeah, it's not possible with UnsavedRows, unfortunately. The reason is that the SQL is dynamically generated based on which Defaulted columns you choose to override, and this will vary row by row. It was a wonder that I got it working with the COPY interface at all, so that's as far as we can go in that direction, unless someone knows something I don't.

The only direct workarounds you have here would be:

• don't use columns with defaulted values when you want to batch insert
• copy/paste the generated code for non-Unsaved Row and adapt it to the default values you do want to plug in (if any)

On the typo side there are things we could do, but it would require codegen changes. One obvious thing would be to disable the functionality where you can override values for defaulted columns. This likely wouldn't be very invasive code-wise, and would lead us to a situation where the query is no longer dynamic per row, and we can get returned rows again.