the bulk/ multiple inserts not always work: ERROR: 42601 PostgresError: syntax error at or near ""0""

[tx0c]

my program is getting multiple rows from another query:

for await (const rows of sql`SELECT <...some-query...>`.cursor(10) {
  // some checking/processing of the rows data,
  // then insert to another table, with
  await sql`INSERT <...another-table> ${sql(rows)}`
}

it turns:

ERROR: 42601 PostgresError: syntax error at or near ""0""

in debugging logs:

INSERT INTO <...another-table> "0"=$1,"1"=$2,"2"=$3

with parameters:

params: [
  { ... }, { ... }, { ... }
]

in this cases rows is a query result Array of 3 elements, but it seems sql(rows) did not recognize it as array of multiple inserts, but consider it as a single object insert instead, and derived 3 keys as 0 1 and 2

the same code of multiple inserts seems working before, but not sure what changes triggered it, and when it happens, what can i do,

   // notice here when the problem happens, the `params` is passed with each raw objects in,

but when the same multiple inserts works before, all objects' values were flattened to pass in values

 params: [
   row0's column0, row0's column1, row0's column2, ...
   row1's column0, row1's column1, row1's column2, ...
   ...
 ]

what's the problem here, how can I tell sql(rows) is a real array of objects instead of single insert object?

[tx0c]

found some clues from other issues mentioned sql.array sql.jsonb not sure related, and can these be documented?

[porsager]

@tx0c could you try the concurrent-cursors branch? (eg npm i porsager/postgres#concurrent-cursors)

[tx0c]

that porsager/postgres#concurrent-cursors looks like correct: generated correct sql with all column values as primitives ($1,$2,$3,...) passed as bind parameters

    'INSERT INTO schema1.table ("id","column1","...")values($1,$2,$3,$4,$5,$6),($7,$8,$9,$10,$11,$12),($13,$14,$15,$16,$17,$18)\n'