Odd rounding

[JohnMount]

From tidyverse/dplyr#3516 . Notice how violently the value is rounded when round-tripped through the database. The amount of rounding depends on the driver (so may not be a pure DBI issue).

db <- DBI::dbConnect(RPostgreSQL::PostgreSQL(),
                     host = 'localhost',
                     port = 5432,
                     user = 'johnmount',
                     password = '')
tmp_df <- data.frame(id = 1363392673615939)
format(tmp_df, scientific = FALSE)
#>                 id
#> 1 1363392673615939
str(tmp_df)
#> 'data.frame':    1 obs. of  1 variable:
#>  $ id: num 1.36e+15
DBI::dbWriteTable(db, "tmp_df", tmp_df, 
                  temporary = TRUE, overwrite = TRUE)
#> [1] TRUE
d <- DBI::dbGetQuery(db, "SELECT * FROM tmp_df")
format(d, scientific = FALSE)
#>   row.names               id
#> 1         1 1363392673615940
DBI::dbGetQuery(db,
                "select table_name, column_name, data_type from information_schema.columns
                where table_name = 'tmp_df' order by table_name, column_name")
#>   table_name column_name        data_type
#> 1     tmp_df          id double precision
#> 2     tmp_df   row.names             text
DBI::dbDisconnect(db)
#> [1] TRUE

[jangorecki]

AFAIR big int always should be passed as text to db drivers, unless they support big int.

https://stackoverflow.com/questions/19169164/r-rpostgresql-bigint-datatype
https://groups.google.com/forum/#!topic/rpostgresql-dev/NDc7NfUP6M8
Looks like I recall good after almost 5 years.

[JohnMount]

I think I see your point. It feels like we should get that last decimal digit (until we have more digits than floating point can represent), but as floating point is in binary things are not that simple (as each stage may round or alter the format for its own notions of safety). That being said, it still seems we are losing one more digit than we should.

[madroxdupe42]

I'd like to second the suggestion that bigint columns be handled as text rather than numeric. I got bitten today when 9 bits of precision were silently dropped from an index column, creating a number of collisions in what should have been a unique ID.

And in the interim, for anyone who needs a workaround you can cast the bigint to text on the database side (e.g. SELECT big_id_column::text FROM table_with_bigint_id) and either keep the character value or convert to the 64-bit integer type supplied by the bit64 package.