Take Care If Trying the RPostgres Package

[This article was first published on R – Win-Vector Blog, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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.


Danger

Danger, Will Robinson!

Trying the new package

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 = '')

## Warning: multiple methods tables found for 'dbQuoteLiteral'

d <- DBI::dbGetQuery(
  db, 
  "SELECT COUNT(1) FROM pg_catalog.pg_tables")
print(d)

##   count
## 1   177

ntables <- d$count[[1]]
print(ntables)

## integer64
## [1] 177

The result at first looks okay.

<span class="kw">class</span>(ntables)

## [1] "integer64"

<span class="kw">typeof</span>(ntables)

## [1] "double"

ntables +<span class="st"> </span>1L

## integer64
## [1] 178

ntables +<span class="st"> </span><span class="dv">1</span>

## integer64
## [1] 178

<span class="kw">is.numeric</span>(ntables)

## [1] TRUE

But it is only okay, until it is not.

<span class="kw">pmax</span>(1L, ntables)

## [1] 8.744962e-322

<span class="kw">pmin</span>(1L, ntables)

## [1] 1

<span class="kw">ifelse</span>(<span class="ot">TRUE</span>, ntables, ntables)

## [1] 8.744962e-322

for(ni in ntables) {
  print(ni)
}

## [1] 8.744962e-322

<span class="kw">unclass</span>(ntables)

## [1] 8.744962e-322

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'
  ")

##   column_name data_type numeric_precision numeric_precision_radix udt_name
## 1       count      real                24                       2   float4

DBI::<span class="kw">dbDisconnect</span>(db)

The work-around

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.

To leave a comment for the author, please follow the link and comment on their blog: R – Win-Vector Blog.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)