Single-statement INSERT a variable number of rows without $1, $2, $3, $...N (since that N is variable)

[wittekm]

Preamble

• I currently use SendBatch, and it works great - just looking to hyper-optimize :)
• For my purposes, I don't believe I can use COPY protocol because this is an INSERT ON CONFLICT.
• This question heavily relates to Insert multiple rows into a table using a single statement #1545 - ctrl-f there for "Next in speed would be one insert statement with multiple rows"; that's exactly the scenario I am describing (and trying to see if there's an un-awkward way to do it).

The question

I have an upsert statement along the lines of

INSERT INTO some_table (row1, row2, modified_at)
VALUES
($1, $2, NOW())
-- the rest of this actually isn't that relevant to the question,
-- just demonstrates that COPY probably wouldn't work
ON CONFLICT ON CONSTRAINT some_constraint DO
UPDATE SET
  row2 = EXCLUDED.row2,
  modified_at = NOW()
WHERE some_table.row2 < EXCLUDED.row2;

Ideally, I'd like to be able to insert multiple rows, but I'm not sure how to idiomatically do that without adding a variable number of placeholders. This is to say, I feel like the string I pass to pgx would have to change based on how many rows I'm upserting:

-- for 1 row: 
VALUES
($1, $2, NOW())

-- for 3 rows: 
VALUES
($1, $2, NOW()), ($3, $4, NOW()),  ($5, $6, NOW())

Is there an idiomatic way to do this in pgx, maybe a test case you could point me at?
And if the answer is "honestly, just use a batch, it's fine" then that's cool with me :)

Meanwhile, in Java land

Just for argument's sake, worth noting that java.sql.PreparedStatement seems to support exactly the behavior I'm looking for:

// UPSERT_STMT is exactly like the above upsert query, except it's `(?, ?, NOW())` instead of `($1, $2, NOW())`

      var stmt = conn.prepareStatement(UPSERT_STMT);
      for (var payload : batch) {
        addPayloadToStatement(payload, stmt);
      }
      stmt.execute();

  private static void addPayloadToStatement(final SomePayload payload, final PreparedStatement stmt) {
    // Believe it or not, this API is 1-indexed, not 0-indexed
    var idx = 0;
    stmt.setString(++idx, payload.row1value);
    stmt.setString(++idx, payload.row2value);
    // addBatch lets you do multiple inserts in a single statement.
    stmt.addBatch();
  }

[jackc]

Ideally, I'd like to be able to insert multiple rows, but I'm not sure how to idiomatically do that without adding a variable number of placeholders. This is to say, I feel like the string I pass to pgx would have to change based on how many rows I'm upserting:

That's correct. It is rather awkward, but it is necessary to use a variable number of placeholders. (At least unless you did something really crazy like using an array of rows / structs that you used unnest to extract in PostgreSQL -- I don't recommend it 😉 )

And if the answer is "honestly, just use a batch, it's fine" then that's cool with me :)

Honestly, just use a batch. 😉 It's far simpler and the speed should be comparable, possibly even better as it may make better use of the statement cache. Also, if there is an error it will be much easier to troubleshoot.

[wittekm]

Got it, shall do then. Thanks for the guidance!