R Data Types to MonetDB Data Types

Question

I am trying to insert a data frame into MonetDB using DBI::dbWriteTable() with the MonetDBLite driver and am having to adjust column types in both the MonetDB table and the R data frame.

I am wondering if there is a mapping of data types from R to MonetDB. For instance, I know (or assume, rather) a character vector in R should map to a MonetDB column of CHAR(n), CHARACTER(n), VARCHAR(n), CHARACTER VARYING(n), TEXT, CLOB, CHARACTER LARGE OBJECT, or STRING and back.

Is there any official documentation on this? Google isn't turning anything up.

Answer 1

There is a DBI function, dbDataType, that determines the database type for a R object. The MonetDBLite driver implements this function. For example,

> library("DBI")
> con <- dbConnect(MonetDBLite::MonetDBLite())
> dbDataType(con, "asdf")
[1] "STRING"
> dbDataType(con, 1L)
[1] "INTEGER"
> dbDataType(con, 1)
[1] "DOUBLE PRECISION"

In case you are interested in the reverse, the R type that is created from a database type, there is also a (private) function in the MonetDBLite driver that does this.

> MonetDBLite:::monetdbRtype("BOOLEAN")
  BOOLEAN 
"logical"