Take care if trying the new RPostgres
database connection package. By default it returns some non-standard types that code
developed against other database drivers may not expect, and may not be ready
to defend against.
One can try the newer RPostgres
as a drop-in replacement for the usual RPostgreSQL
.
That starts out okay. We can connect to the database
and and pull a summary about remote data to R
.
db <- DBI::dbConnect( RPostgres::Postgres(), host = 'localhost', port = 5432, user = 'johnmount', password = '') d <- DBI::dbGetQuery( db, "SELECT COUNT(1) FROM pg_catalog.pg_tables") print(d) ntables <- d$count[[1]] print(ntables)
The result at first looks okay.
class(ntables) typeof(ntables) ntables + 1L ntables + 1 is.numeric(ntables)
But it is only okay, until it is not.
pmax(1L, ntables) pmin(1L, ntables) ifelse(TRUE, ntables, ntables) for(ni in ntables) { print(ni) } unclass(ntables)
If your code, or any package code you are using, perform any of the above calculations,
your results will be corrupt and wrong. It is quite likely any code
written before December 2017 (RPostgres
's first CRAN
distribution)
would not have been written with the RPostgres
"integer64
for all of my friends" design decision in mind.
Also note, RPostgres
does not currently appear to write integer64
back to the database.
DBI::dbWriteTable(db, "d", d, temporary = TRUE, overwrite = TRUE) DBI::dbGetQuery(db, " SELECT column_name, data_type, numeric_precision, numeric_precision_radix, udt_name FROM information_schema.columns WHERE table_name = 'd' ")
DBI::dbDisconnect(db)
The work-around is: add the argument bigint = "numeric"
to your dbConnect()
call. This is mentioned in the manual, but not the default and not called out in
the package description or README
. Or, of course,
you could use RPostgreSQL
.
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.