How to handle other column types in rows_append() and rows_insert()?

[mgirlich]

Inserting in a jsonb column is not that easy

library(dplyr, warn.conflicts = FALSE)
library(dbplyr, warn.conflicts = FALSE)

con <- DBI::dbConnect(RPostgres::Postgres())

if (DBI::dbExistsTable(con, "..json_test")) {
  DBI::dbRemoveTable(con, "..json_test")
}

DBI::dbExecute(
  con,
  'CREATE TABLE "..json_test" (
    id integer NOT NULL,
    json_field jsonb
  )'
)
#> [1] 0

data.frame(id = 1, json_field = "[1, 2, 3]") %>% 
  rows_append(
    x = tbl(con, "..json_test"),
    copy = TRUE,
    in_place = TRUE
  )
#> Error: Failed to fetch row: ERROR:  column "json_field" is of type jsonb but expression is of type text
#> LINE 2: SELECT *
#>                ^
#> HINT:  You will need to rewrite or cast the expression.
DBI::dbReadTable(con, "..json_test")
#> [1] id         json_field
#> <0 rows> (or 0-length row.names)

# need to explicitly copy and cast beforehand
copy_inline(con, data.frame(id = 1, json_field = "[1, 2, 3]")) %>% 
  mutate(json_field = sql("json_field::jsonb")) %>% 
  rows_append(
    x = tbl(con, "..json_test"),
    in_place = TRUE
  )
DBI::dbReadTable(con, "..json_test")
#>   id json_field
#> 1  1  [1, 2, 3]

^{Created on 2022-06-08 by the reprex package (v2.0.1)}

Maybe we can add explicit casts. Otherwise, we should add an example how to deal with this yourself

[mgirlich]

The casting could be done via something like

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
DBI::dbWriteTable(con, "test", tibble::tibble(int = 1L, dbl = 0.5, txt = "a"))
res <- DBI::dbSendQuery(con, "SELECT * FROM test")
DBI::dbFetch(res, n = 0)
#> [1] int dbl txt
#> <0 rows> (or 0-length row.names)
col_info <- DBI::dbColumnInfo(res)
DBI::dbClearResult(res)

DBI::dbGetQuery(
  con,
  paste0(
    "SELECT\n  ",
    paste0("CAST(", col_info$name, " AS ", col_info$type, ") AS ", col_info$name, collapse = ",\n  "), "\n",
    "FROM test"
  )
)
#>   int dbl txt
#> 1   1 0.5   a

^{Created on 2022-08-03 by the reprex package (v2.0.1)}