Can't append data to sqlite3 with dplyr db_write_table()

Question

Looking into adding data to a table with dplyr, I saw https://stackoverflow.com/a/26784801/1653571 but the documentation says db_insert_table() is deprecated.

?db_insert_into()

...
db_create_table() and db_insert_into() have been deprecated in favour of db_write_table().
...

I tried to use the non-deprecated db_write_table() instead, but it fails both with and without the append= option:

require(dplyr)

my_db <- src_sqlite( "my_db.sqlite3", create = TRUE)                 # create src
copy_to( my_db, iris, "my_table", temporary = FALSE)                 # create table
newdf = iris                                                         # create new data

db_write_table( con = my_db$con, table = "my_table", values = newdf) # insert into
# Error: Table `my_table` exists in database, and both overwrite and append are FALSE

db_write_table( con = my_db$con, table = "my_table", values = newdf,append=True) # insert into
# Error: Table `my_table` exists in database, and both overwrite and append are FALSE

Should one be able to append data with db_write_table()?

See also https://github.com/tidyverse/dplyr/issues/3120

Answer 1

No, you shouldn't use db_write_table() instead of db_insert_table(), since it can't be generalized across backends.

And you shouldn't use the tidyverse versions rather than the relevant DBI::: versions, since the tidyverse helper functions are for internal use, and not designed to be robust enough for use by users. See the discussion at https://github.com/tidyverse/dplyr/issues/3120#issuecomment-339034612 :

Actually, I don't think you should be using these functions at all. Despite that SO post, these are not user facing functions. You should be calling the DBI functions directly.
-- Hadley Wickham, package author.