R RpostgreSQL bigint datatype

Question

I need to deal with the, I suppose the big int, primary key values 1380742793415240. In R I can easily adjust option(scipen=100) but I need to store that data in postgres db. I've already tried to dbWriteTable default (double precision) with result: violate constaint (probably duplicate keys in ...4e+015 representation) and also after changing target column to bigint with result: invalid input syntax for integer: "1.38074279341524e+015".

example: try to save and load from db following dt

sample_dt <- data.table(a = c(20130101,20130102,20130102), 
                        b = c(1380742793415240,1380742793415241,1380742793415242))

What is the effective way of save and load this kind of data in postgres?

Save as character? (It's `options`' and I'm wondering if `options(scipen=100)` is really doing what you think it is.) `.Machine$integer.max < 1380742793415240 [1] TRUE` — IRTFM, Oct 03 '13 at 22:03
Using character field for that I would not be able to query `sql <- paste0("select * from tbl where b >= '",one_id,"'")` effectively. Not all the IDs are are starting with 138..., to handle that case I would need to fill the leading spaces with zeros to have all the strings the same nchar. Isn't there any more elegant solution to store numbers like `212982,1380742793415240` as the primary key column and query them effectively using `>` operator??? — jangorecki, Oct 04 '13 at 08:22
I don't really know. I do know that your proposed key will not fit inside the constraints of the integer mode of R. — IRTFM, Oct 04 '13 at 16:28

jangorecki · Accepted Answer · 2015-09-22T12:00:57.863

The subject was well discussed and resolved on RpostgreSQL mailing list, linking it if anybody would have same problem: https://groups.google.com/forum/#!topic/rpostgresql-dev/NDc7NfUP6M8
Below the content:

library(RPostgreSQL)
# Loading required package: DBI
c=dbConnect("PostgreSQL")
a <- 1380742793415240
b <- 1380742793415241
dc <-data.frame(a=as.character(a), b=as.character(b))
dbWriteTable(c,"testclosenumberch", dc)
# [1] TRUE
dbGetQuery(c, "select * from testclosenumberch")
#   row.names                a                b
# 1         1 1380742793415240 1380742793415241
dbGetQuery(c, "select a::bigint - b::bigint  from testclosenumberch")
#   ?column?
# 1       -1

R RpostgreSQL bigint datatype

1 Answers1