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
– IRTFM, Commented Oct 3, 2013 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
– jangorecki, Commented Oct 4, 2013 at 8: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
– IRTFM, Commented Oct 4, 2013 at 16:28

jangorecki · Accepted Answer · 2015-09-22 12:00:57Z

3

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

edited Sep 22, 2015 at 12:00

answered Oct 4, 2013 at 22:37

jangorecki

16.8k5 gold badges86 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

R RpostgreSQL bigint datatype

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related