Solve DBI returning integer64 in R

Recently I bumped into an issue where a query, ran using the DBI library with the dbGetQuery function returned an integer64. Even without me noticing it. When converting my data frame (or data.table) to a matrix for clustering purposes, I ran into the following error:

Error in if (changes == 0) break : missing value where TRUE/FALSE needed

After inspection of the table, I found that the data.matrix() function was unable to automatically convert integer64 values.

You can solve this issue retroactively by converting the integer64 into a double, as follows (data.table syntax):

dt[,COLUMN_NAME := as.double(COLUMN_NAME)]

However, you can solve the issue proactively by setting a specific parameter when connecting to the database.

dbConnect(db, bigint = 'integer', ...)

You can find more about it in the DBI documentation:

Great success!