Set dbGetQuery to return integer64 as a whole

Question

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

Then I use dplyrto try to filter and process my results, but get into problems because it dplyrdoes not support type objects integer64.

Is it possible to set dbGetQuery()to return columns integer64as a class integer?

Answer 1

Even without full support for 64-bit integers (see Problem with GitHub ), you can still use dplyr to mutate 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

Answer 2

The DBI specification provides this functionality through an argument bigint. Support will obviously vary between drivers.

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

The following values ​​behave as follows:

"integer": always return as an integer, silently overflowing

"numeric": always return as numeric, silently

character: always return decimal representation as a character

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

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