Search code examples
rintegerodbcdplyrr-dbi

Set dbGetQuery to return integer64 as integer

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?

Solution

  • 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