BIGINT read/write support

[natecobb]

• On read RPostgres BIGINTs silently get truncated to 4 bytes but show up as R double types in the returned data frame. I'm guessing that they get cast as R integers here:
  https://github.com/rstats-db/RPostgres/blob/7ffe0d6bb3f72939461ba386af114cd9357311d6/src/PqResult.h#L257
  but later recast as double.
• dbWriteTable with COPY seems to use scientific notation when formatting numbers (based on the error below). For some reason this generates an error when inserting a large number into a BIGINT; this doesn't happen if inserting into a numeric type or if COPY is disabled and SQL is generated.
• Less important, dbWriteTable selects 4 byte REAL type for R double types, which can also cause silent data corruption on write. This should probably be a 8 byte "DOUBLE PRECISION" type.
  https://github.com/rstats-db/RPostgres/blob/eb064d445e5c4b7613e5c9d062676b545f737306/R/connection.R#L106

library(DBI)
library(RPostgres)

show_table = function(conn, table = "test_bigints") {
  dbGetQuery(conn, 
             sprintf("select column_name, data_type as field_type, character_maximum_length
                from INFORMATION_SCHEMA.COLUMNS where table_name = %s;", dbQuoteString(conn, table)))
}

conn <- dbConnect(RPostgres::Postgres())
x <- data.frame(db_variables = c(127, 32767, 8388607, 2147483647, 4294967295))

# Basic case
# RPostgres::dbWriteTable selects REAL (4 bytes) when creating a table. 
# This corrupts silently on insert.
# Same effect if we disable COPY
dbWriteTable(conn, "test_bigints", x, temporary = TRUE, overwrite = TRUE, copy = TRUE)
y = dbGetQuery(conn, "SELECT db_variables FROM test_bigints")
show_table(conn)
cbind(x$db_variables, typeof(x$db_variables), y, (y == x$db_variables))

# What if we create our own table with a correct type?
# Seems to work, but looks like we have a problem getting them back
dbExecute(conn, "DROP TABLE IF EXISTS test_bigints ")
dbExecute(conn, "CREATE TEMPORARY TABLE test_bigints (db_variables INT8)")
dbWriteTable(conn, "test_bigints", x, temporary = TRUE, append = TRUE, copy = TRUE)
y = dbGetQuery(conn, "SELECT db_variables FROM test_bigints")
show_table(conn)
cbind(x$db_variables, typeof(x$db_variables), y, (y == x$db_variables))

# Looks like BIGINTs are getting cast wrong, works fine if we 
# pull them down as numeric
y = dbGetQuery(conn, "SELECT db_variables::numeric FROM test_bigints")
show_table(conn)
cbind(x$db_variables, typeof(x$db_variables), y, (y == x$db_variables))

# What if we go bigger, to a true BIGINT? We throw an error for invalid syntax!
local_variables_2 = c(x$db_variables, 1051583408235890)
x2 <- data.frame(db_variables = local_variables_2)
dbWriteTable(conn, "test_bigints", x2, temporary = TRUE, append = TRUE, copy = TRUE)

#Error in eval(substitute(expr), envir, enclos) : 
#  ERROR:  invalid input syntax for integer: "1.05158340823589e+15"
#CONTEXT:  COPY test_bigints, line 6, column db_variables: "1.05158340823589e+15"

# But not if we use SQL statements rather than a COPY stream
dbExecute(conn, "TRUNCATE TABLE test_bigints")
dbWriteTable(conn, "test_bigints", x2, temporary = TRUE, append = TRUE, copy = FALSE)
y = dbGetQuery(conn, "SELECT db_variables::numeric FROM test_bigints")
show_table(conn)
cbind(local_variables_2, y, y == local_variables_2)

# Oddly enough we can write with COPY into a DOUBLE PRECISION data type
dbExecute(conn, "DROP TABLE IF EXISTS test_bigints ")
dbExecute(conn, "CREATE TEMPORARY TABLE test_bigints (db_variables DOUBLE PRECISION)")
dbWriteTable(conn, "test_bigints", x2, temporary = TRUE, append = TRUE, copy = TRUE)
y = dbGetQuery(conn, "SELECT db_variables::numeric FROM test_bigints")
show_table(conn)
cbind(local_variables_2, y, y == local_variables_2)

dbDisconnect(conn)

[parisni]

Hi, is there any plan for those 8bytes types problems ?

I could find some time to work on a PR, but maybe this takes 5 min to @hadley

[parisni]

See #112

[krlmlr]

Thanks. 64-bit integers will be available via the bit64::integer64 data type, and optionally as numerics/integers and strings.

[krlmlr]

Should work now.

[rsaporta]

Can confirm this works. Thanks for the update.

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.