rows_insert requires that for each row, the value in each column must be different from everything else in that column

[wdenton]

Either I'm misunderstanding the documentation or there's a bug in rows_insert(). If the former (much more likely), I'm happy to try to patch the documentation.

The docs for rows_insert() say (from dplyr), "rows_insert() adds new rows (like INSERT). By default, key values in y must not exist in x." Does this mean that in any table, for each row, the value in each column must be different from everything else in that column? Surely that isn't right, yet it seems to be the case:

suppressPackageStartupMessages({
  library(dplyr)
  library(dbplyr)
  library(DBI)
})

con <- DBI::dbConnect(RSQLite::SQLite())

# Create database with one table and three columns: id, fruit, colour
create_db <- "CREATE TABLE fruits(id INTEGER PRIMARY KEY AUTOINCREMENT, fruit TEXT, colour TEXT)" |> as.sql(con)
DBI::dbExecute(con, create_db)
#> [1] 0

fruits <- tbl(con, "fruits")

# Apples can be red
red_apple <- tibble(fruit = "apple", colour = "red")

# Insert into the database; primary key autoincrements and id will be assigned
rows_insert(fruits, red_apple, copy = TRUE, conflict = "ignore", in_place = TRUE)
#> Matching, by = "fruit"

fruits
#> # Source:   table<fruits> [1 x 3]
#> # Database: sqlite 3.41.2 []
#>      id fruit colour
#>   <int> <chr> <chr>
#> 1     1 apple red

# Apples can also be green
green_apple <- tibble(fruit = "apple", colour = "green")

# This new row should be added to the database
rows_insert(fruits, green_apple, copy = TRUE, conflict = "ignore", in_place = TRUE)
#> Matching, by = "fruit"

# But it isn't!
fruits
#> # Source:   table<fruits> [1 x 3]
#> # Database: sqlite 3.41.2 []
#>      id fruit colour
#>   <int> <chr> <chr>
#> 1     1 apple red

# To be added both values must be different from anything in the table
lemon <- tibble(fruit = "lemon", colour = "yellow")
rows_insert(fruits, lemon, copy = TRUE, conflict = "ignore", in_place = TRUE)
fruits
#> # Source:   table<fruits> [2 x 3]
#> # Database: sqlite 3.41.2 []
#>      id fruit colour
#>   <int> <chr> <chr>
#> 1     1 apple red
#> 2     2 lemon yellow

[mgirlich]

Indeed, you misunderstood the documentation. In your case the key value is the column fruit. As stated in the documentation Keys typically uniquely identify each row. This means that you can only insert different fruits.
If you want to insert more apples you can use rows_append().

[mgirlich]

I created an issue in dplyr that the documentation of rows_insert() seems to be a bit confusing.