My best guess is that this simply isn't currently supported by the {duckdb} package, however I'm not sure if I'm doing something wrong/not in the in the intended way. Here's a reprex which reproduces the (fairly self-explanatory) issue:
con <- DBI::dbConnect(duckdb::duckdb())
# Note: this connection would work fine
# con <- DBI::dbConnect(RSQLite::SQLite())
DBI::dbCreateTable(
conn = con,
name = "raw_test",
fields = list(file = "blob")
)
DBI::dbAppendTable(
conn = con,
name = "raw_test",
value = data.frame(file = I(list(as.raw(1:3)))),
field.types = list(file = "blob")
)
#> Error: rapi_execute: Unsupported column type for scan
#> Error: rapi_register_df: Failed to register data frame: std::exception
NB (1), I'm trying to find a way to write arbitrary R objects to SQL. To do this, I plan to serialise the objects in question to binary format, write to SQL, read back and unserialise. I also want to find a method that works reliably with as many SQL backends as possible, as I'm planning to create a package which allows the user to specify the connection.
NB (2), I've posted this as an issue on the duckdb github as I have a feeling this is simply a bug/not yet a supported feature.
Edit #1
I'm now more convinced that this is simply a bug with {duckdb}. From the documentation for DBI::dbDataType():
If the backend needs to override this generic, it must accept all basic R data types as its second argument, namely logical, integer, numeric, character, dates (see Dates), date-time (see DateTimeClasses), and difftime. If the database supports blobs, this method also must accept lists of raw vectors, and
blob::blobobjects.
duckdb certainly supports blob types, so as far as I can see, these objects should be writeable. Note, this code produces the same issue outlined above (using blob::blob() instead of I(list()):
DBI::dbAppendTable(
conn = con,
name = "raw_test",
value = data.frame(file = blob::blob(as.raw(1:3))),
field.types = list(file = "blob")
)
#> Error: rapi_execute: Unsupported column type for scan
#> Error: rapi_register_df: Failed to register data frame: std::exception
I'm leaving this open for now in case any kindly duckdb dev can confirm this is a bug/missing feature, or if anyone can suggest a workaround.
