Set dbGetQuery to return integer64 as integer

Question

By default when I use dbGetQuery() from the DBI package it returns columns of type integer64 as the integer64 class of the bit64.

I then use dplyr to try and filter and manipulate my results but come into issues as dplyr does not support objects of type integer64.

Is it possible to set dbGetQuery() to return integer64 columns as class integer?

Answer 1

The DBI spec provides this functionality via the bigint argument. Support will obviously vary between drivers.

dbConnect(drv, bigint="integer", ...)

The following values behave as follows:

"integer": always return as integer, silently overflow

"numeric": always return as numeric, silently round

"character": always return the decimal representation as character

"integer64": return as a data type that can be coerced using as.integer() (with warning on overflow), as.numeric() and as.character()

Source: https://cran.r-project.org/web/packages/DBI/vignettes/spec.html#_specification_17

Answer 2

Even without full support of 64-bit integers (see GitHub issue), you still can use dplyr to mutate away from integer64:

library(dplyr, warn.conflicts = FALSE)
df <- data_frame(a = bit64::as.integer64(1:3), b = 1:3, c = 1.5:4)
df
#> # A tibble: 3 x 3
#>                 a     b     c
#>   <S3: integer64> <int> <dbl>
#> 1               1     1   1.5
#> 2               2     2   2.5
#> 3               3     3   3.5
df %>% mutate_if(bit64::is.integer64, as.integer)
#> # A tibble: 3 x 3
#>       a     b     c
#>   <int> <int> <dbl>
#> 1     1     1   1.5
#> 2     2     2   2.5
#> 3     3     3   3.5