Get IDs or count of newly upserted rows in MariaDB

Thank you for this super handy library!

Want to check if there was a way to get the number of rows inserted when using the dbxUpsert in MariaDB. This would help tell the user about the count of rows where was inserted either via logs or via popups in case of data uploads. I tried the option returning=c("id") and like mentioned in the documentation, it did not work on MariaDB and instead failed with this error.

records <- dbx::dbxUpsert(
  conn = con,
  table = table_name,
  records = data,
  where_cols = c("vehicle", "wts"),
  batch_size = 1000,
  returning=c("id")
)

Error in selectOrExecute(conn, sql, batch, returning = returning) : 
  returning is only supported with Postgres

Documentation

Ref: https://github.com/ankane/dbx/

If you use auto-incrementing ids in Postgres, you can get the ids of newly upserted rows by:

dbxUpsert(db, table, records, where_cols=c("id"), returning=c("id"))

ankane / dbx

Get IDs or count of newly upserted rows in MariaDB #37

Documentation